Database naming conventions

[eluser]Thorpe Obazee[/eluser]
I've already gone with this convention:

Table names:
plural

Field names:
As simple as it could. eg. name, description, etc.
I usually use the "AS" keyword when collisions occurs.

Foreign Keys:
Singular table name of reference table with the suffix, '_id'

[eluser]jedd[/eluser]
waldmeister - I realise that I perhaps didn't fully grok your FK handling. You say that it has the same name as the field you're talking to, but I'm not sure how that resolves with your standard of prefixing each field with the owning table name. Does that mean an FK will have two table names embedded in its own name?

Dam1an - I gather that people coming from an ER modeling / data mapping / theoretical / classic-trained style background are more likely to see these (tables) in the singular, for fairly obvious reasons. I ack that there's no definitive right way and that this is yet another of those mighty jihad-inspiring discussions that no one can ever 'win' (though vi really is better than emacs), so consistency is probably the key here. I discovered an interesting comment on some blogger's site - he observed that if you have a bag of apples, you don't write 'bags' on the outside. It's a very poor metaphor, I know, but then a table name that tries to describe a bunch of columns is necessarily similarly disconnected.

garycocs - when you say "foreign key" does this mean you're not really using a foreign key, but a pseudo-FK? My understanding is that even now, MySQL does not force FK compliance, though I think I've seen suggestions that FK constraints will be added in an up-coming release of MySQL. (Though who knows what will happen with MySQL now that Larry owns it, eh?)

There are several ways of doing what you're trying to do there in a single SELECT statement. An ugly but very loose translation of your two statements would be:

Code:

SELECT * FROM city, pub
WHERE city.id = $cityid
AND pub.city_id = $cityid

In reality though you'd probably do a LEFT JOIN instead, and we'd need to see your schema - and have a clear idea of what you were trying to achieve - to give a more precise response,but perhaps something like:

Code:

SELECT * FROM city
LEFT JOIN pub ON pub.city_id = $cityid

[eluser]davidbehler[/eluser]
jedd, that's exactly how I do it. e.g. let's say i have a table user with primary key user_id and a table group with primary group_id, then the linking table would be user_group with the primary key user_group_id and 2 more columns named user_group_user_id and user_group_group_id.

[eluser]jedd[/eluser]
waldmeister - wow!

Even though I type at around 80wpm, that approach makes my toes curl.

But whatever works, I suppose - and ultimately consistency will tie with brevity.

[eluser]davidbehler[/eluser]
Well...it might be more to write but atleast I'm consistent
And as said before I don't have to worry about duplicate field names (with linking tables that might not be much of a problem because they usually don't hold much more info than a couple of primary keys and if you join the linking table with the linked tables the only case of duplicate field names would propably the primary keys be...).

Maybe I will actually adjust my rules and not prefix my FKs at all...that kinda makes sense but I will have to think about it

[eluser]Dam1an[/eluser]
[quote author="jedd" date="1242302186"]
Dam1an - I gather that people coming from an ER modeling / data mapping / theoretical / classic-trained style background are more likely to see these (tables) in the singular, for fairly obvious reasons. I ack that there's no definitive right way and that this is yet another of those mighty jihad-inspiring discussions that no one can ever 'win' (though vi really is better than emacs), so consistency is probably the key here. I discovered an interesting comment on some blogger's site - he observed that if you have a bag of apples, you don't write 'bags' on the outside. It's a very poor metaphor, I know, but then a table name that tries to describe a bunch of columns is necessarily similarly disconnected.[/quote]

Not sure I agree with you at all there
I was originally self taught through the internet and the code Dreamweaver auto generated for me (which looking back on it, makes me sick), I then studied advanced databases and ER modelling at Uni... and I've never yet had anyone say they should be singular for obvious reasons

And I don't see how the bag of apples (or should that be bags of apple ) anaolgy holds
If I have a database table about apples, then the table will be called apples (the plural object), not the container (bag(s))

[eluser]jedd[/eluser]
My point about ER modeling is that, notwithstanding n:n relationships, you tend to deal with individual entities. The fact that in reality you have a data store of many is immaterial to the modeling, which by definition (as per previous caveat) assumes one entity on one or both sides of each relationship.

As you said earlier, if you were selecting a user based on their email address (ie. you were anticipating retrieving a single record) then singular would make more sense to you.

Extrapolating those two points - even though you might move around arrays of multiple entities, in very general terms you will tend to deal with one entity at a time (otherwise you're dealing with aggregated data, which isn't really the original entity in any case).


And yes, on further reflection the bag analogy is pretty blowy. However ...

If I have a database table about apples, then the table will be called apples (the plural object), not the container (bag(s))

Your columns (which you've already asserted are singular) will describe a single row, yet you believe the table must describe many rows? EDIT - to clarify - I believe this logic is flawed, as the column name describes precisely as many rows as the table name does, by definition. /EDIT

If you were designing a database, and knew that one table would pretty much only have one row in it forever, would you make an exception and give it a singular name?

[eluser]Dam1an[/eluser]
[quote author="jedd" date="1242337155"]If you were designing a database, and knew that one table would pretty much only have one row in it forever, would you make an exception and give it a singular name?[/quote]

I don't beleive there is such a case.
Not because I can't beleive there to be a case where you would have just the one row, but because I wouldn't beleive them when they said it will always be just the one row... The case of a client saying "We'll never need a permissions system" spring to mind (think everyone has had something like that)

[eluser]garycocs[/eluser]
Hi jedd thanks for the reply, if you want a better understanding of the DB you could have a look at www.ratemypub.ie for an understanding.

Basically my tables are

county
pub
rate
pic
.....

every pub is in a county so I use (I guess a pseudo??) foreign key pub_id, not sure how to set up the hardcoded foreign key????

so on the home page etc I display counties and maybe a pub count from pubs on the pub page i display pub info ratings form rate and pics from pic etc.

when adding a rating I just insert a new row into rates etc.

This make sense?

[eluser]slowgary[/eluser]
My two cents (1.5 actually) on the plural vs singular:

I don't really understand the singular argument. A table is a collection of items. So 'table of users' makes sense, just as a 'bag of apples'. If you have a 'bag of apples' and you eat all but one apple, it's still a 'bag of apples'. "Bob, would you fetch me the last apple from the bag of apples?". "Sorry Jim, I already ate the last apple from the bag of apple". "Bag of apple? Bob, are you smoking crack again? That's a 'bag of apples'".

So when I look at a query that says "SELECT * FROM `user` WHERE `salary` > 20000" it makes me think, 'Does this user have more than 1 job? When a table name is singular, it no longer represents a collection of objects. If we can omit plurality for table names, why can't we do the same in real life? "Sir, I would like a large order of nacho, and a side of refried bean. Make that 2 side of refried bean".

Thanks guy for reading my word.