Using databases

When translating an OO design to a relational model there are several ways of mapping an inheritance tree. Suppose B and C both inherit from A:

Mapings
1. Three tables fully denormalized (a lot of overlap)
2. Three tables: Table A containing a foreign key to a B or a C (complex queries)
3. Two tables (A is abstract, B and C overlap)
4. One table that contains all occurences and therefore has a lot of empty fields (ugly)

Cheers

You don't want to model byte for byte the data structures you have in your game, to your database. Only choose what you need and in a lot of cases you won't even have to have relations and multiple tables where in code you might have inheritence or composition...

For example, just have a Character table with things like name, position, etc... theres no need to do anything weird here like you have to in your code. If you want to share common things like name and description, just have a seperate table called "game element", with "name", "description", and "game_element_id" as members. Give your Character table a "game_element_id" member as a foreign key to link the 2 togeather.

Graveyard's suggestion what what I have been considering but my only concern with this is the name is going to be the most used item in the game (text based). When ever you do anything with an item, use access it using its name (if there are multiples you use second, third, etc).

Because of this, I've been looking at most items being considered a Game Element until more specific things are needed. When more specific things are needed, the Game Element entry will need to know what more specific item it is, requiring both a "my specific" table and ID field. I haven't heard of having a reference to another table in a field before and am not sure if this is a good idea or not...

Quote:

Original post by Drethon Graveyard's suggestion what what I have been considering but my only concern with this is the name is going to be the most used item in the game (text based). When ever you do anything with an item, use access it using its name (if there are multiples you use second, third, etc). Because of this, I've been looking at most items being considered a Game Element until more specific things are needed. When more specific things are needed, the Game Element entry will need to know what more specific item it is, requiring both a "my specific" table and ID field. I haven't heard of having a reference to another table in a field before and am not sure if this is a good idea or not...

The last line of your post sounds to me like you don't understand what foreign keys and relations are... You should use these as much as possible and everywhere it makes sence.

You are correct about the "my specific" table and ID field. Have the specific table have a game_element_id field which is a foreign key pointing to the game element table.... then you can easily get the more specific info from by just doing "select * from some_specific_thing where game_element_id = " + game_element_id. You can easily tell if an item or whatever has more specificness qualities to it by just checking each specific table with the given game element ID.

You should use relations as much as possilble for lots of reasons... For example if your character can have a status such as sick or dead, then instead of making a "status" varchar field in the character table, make a "status_id" field which is a FK pointing to a status table, which has entries in it such as "sick" and "dead"... This keeps things nice and airtight.... kind of like using an enum instead of a const int...

I know more or less about relationships but nothing about foreign keys. I'm using this project to lear databases among other things so this helps.

Thanks.

Quote:

Original post by graveyard filla You should use relations as much as possilble for lots of reasons...

I agree, except for the "as much as possible" part, I would word it a little differently.

Be careful, a common mistake is to over normalize the database, which can cause problems when tables get large and have to be joined to 5 other tables which are also large (contain many records). That's a lot of table scans to find one record of data. Indexes help with this, but if you index 5 fields in a table you probably are using more storage space than you saved by not storing redundant data, and it also kills performance when adding or deleting records, or editing fields with indexes in that table. If you are concerned about performance a healthy mix between normalization and data redundancy can go a long way. Sometimes storing actual info rather than ID's is the best way to maintain historic integrity, but for a game I don't see why that would really come into play.

Just my 2 cents