CREATE TABLE players (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
– other fields aplenty…
);
CREATE TABLE objects (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
– other properties objects can have
);
CREATE TABLE players_objects (
player_id INTEGER NOT NULL REFERENCES (players.id),
object_id INTEGER NOT NULL REFERENCES (objects.id),
count INTEGER
);
SELECT * FROM objects JOIN players_objects
WHERE player_id = (SELECT DISTINCT name FROM players WHERE name = 'Bob')
ORDER BY id
INSERT INTO players_objects(player_id, object_id, count) VALUES (
(SELECT id FROM players WHERE name = 'Bob'),
(SELECT id FROM objects WHERE name = 'apple'),
3
)
An example, not every entity will have strength, dexterity, constitution. An entity may be an object and have a completely different set of stats. However, my code still reads it as an entity. And even then, an object could have a different set of stats from another.
EDIT: Would I save the stats to another table with each stat is its own row?