Database Naming Conventions

[codestryke]

Though my journeys with rewriting games and such I've found that very few people use proper naming conventions for database tables. It's something very simple but often over looked. Please note I did not make these standards up, these are from actually working in a production environment developing software. A lot of development houses have there own small changes but they all stick to the same base rules.

Ok so you have a player table, each player has a weapon or weapons (the weapons need to be in a separate table). You would have two tables, the player table doesn't have to be called player it could be called users, accounts whatever. We are just using player for this example

player <-- holds all the data for the player account
player_weapons <-- holds all the data for each players weapons

The weapons table should NOT be called just weapon or weapons. Since the data in that table is relative to the player account you preface the table with the main table it references. Yes I know, simple right? But so many developers don't do this and it makes looking at tables and table references very cumbersome. Now if you had an attack log table what should it be called? That's right player_attacklog, not just attack log!

This lets others or even yourself look at the player_attacklog table and know that it relates to players. If it's just called attacklog, then really what do you know about it? Attacklog could be logging for admins, it could be anything and thats the point. At a glance you should have a good understanding of what the table relates to just by the name.

[Scion]

Generally i agree with 90% of what you write here....but not this time.

I dont want to start a religious war but I would use a table called player with the PK of id and a weapon (singular) table with a pk of id (yep i allways name my PK id ) and a FK to the player table called player_id (or generically <table name>_id. Im not too keen on for your example because what happens when you have another table that references the weapons.....should i prefix it with player_weapons_mytable or not? or what happens when i have multiple FK references. The other downside form my point of view is what happens when 3 weeks later i decide its unfair that only players get to have weapons and i want to add NPC weapons......do i just use the player_weapon table? do i add a new table with almost identical structure...

But this is all so much Würst, Because the most important point ( that i think we'd both agree on) is that you should allways, allways use a naming/coding convention....

which one, in the end is a matter of taste...but use one.....even if no-one but you is ever going to read your code.....using a convention will make understanding that complcated role based movement system you developed 6 months ago so much easier to understand when you decide it needs a modification.

[codestryke]

Yes we'll agree to disagree on this one 

With your above example if I were designing the database (assuming each player could have multiple weapons) it would be.

player <- main player table (pk = playerid)
weaponlu <- weapons and there stats (pk = weaponid)
player_weapons <- Link table that stores the one to many relationship between playerid -> weaponid

With NPC's that would cause a problem but the answer would be dependent on how the NPC's are stored in the database.

The ideas posted in the original post were to be able to look at a table names and at least get an idea of what is going on with the data being stored in each table. Without them a person either has to go though every table and look at where all the FK references are or use some sort of ERD program to model all the data for you.

Following a consistent naming convention though we agree on.

[bbgames]

I think you may have misinterpreted, Scion(although I might be here too, so take it with a grain of salt until the original author responds).

codestryke isn't saying that every piece of data on your weapons should go into the player_weapons table - just the information relating to both a player and their weapons. If a player has 3 of a sword, that would go into player_weapons - or anything that the player can do to their single copy of that weapon. The weapon data itself would still live in weapons, so that other tables like monster_weapons and shop_weapons could refer to the same base data as well.

Then again, I could be wrong - codestryke, care to expand?

[Scion]

concurency.....gotta love it .......or hate it....i mostly hate it.....but only cause i gotta deal with makin it safe

well now if were talking link tables for creating a many-to-many relationship well yes I too name them fromTable_toTable so it would be player_weapon (note im still singular)....and have a pk of player_weapon_id and fk references of player_id and weapon_id.....

and yes, now that you pointed it out bbgames....i think i did interpret that original post differently (I interpreted it for a one-to-many relationship) .....so....does that mean we all actually agree after all....

dang....

[codestryke]

My bad.. My original post didn't make it more clear on what I was trying to say.. I need to take more time thinking before typing LOL.

Ya looks like we agree after all

[JGadrow]

The only time I violate this rule is if the relationship is already implied.

IE: I don't have a table called 'user_player', it's just 'player'

[Tribal]

I am "uber n00b" what is "NPC", "PK" and "FK" ?? 

[toxin]

I am still trying to learn the best way to do the database the above helps out a lot thanks.

[Scion]

I am "uber n00b" what is "NPC", "PK" and "FK" ?? 

NPC = Non Player Character (I bet you knew that one already)
PK = Primary Key of a table (usually some sort of id column, since each one is normally unique)
FK = Foreign Key, a column in a table that matches a PK in another table (to create a refence)

& ERD = Entity Relationship Diagram.....helps visualise the structure/relationships of a databases tables.

[Tribal]

I am "uber n00b" what is "NPC", "PK" and "FK" ?? 

NPC = Non Player Character (I bet you knew that one already)
PK = Primary Key of a table (usually some sort of id column, since each one is normally unique)
FK = Foreign Key, a column in a table that matches a PK in another table (to create a refence)

& ERD = Entity Relationship Diagram.....helps visualise the structure/relationships of a databases tables.

Thanks dude