Database naming conventions

[eluser]Thorpe Obazee[/eluser]
slowgary, that's a fine read. It's also funny (with the smoking crack part). And yours and jedd's comments are most logical imho. I actually never thought of things that way but I already follow the plural tables convention.

[eluser]Dam1an[/eluser]
That was indeed a fine read, although I think you need some more grammer lessons

"Sir, I would like a large order of nacho, and a side of refried bean. Make that 2 side of refried bean"

Complete lack of understanding about singular/plural

[eluser]jedd[/eluser]
I would really have to wander over to my book shelf (or for you guys, think books shelves, as it contains many books and also has more than one shelf in it) to check my OED grammar references ...

Alas, the language has lots of idiosyncrasies, and it's easy to identify any number that substantiate your particular POV on the day. I was thinking about this earlier today, as I got some socks from my sock drawer, and then grabbing a few coins from our coin bowl on the way out.

Anyhoo .. slowgary, you write:

A table is a collection of items.

This aligns with my earlier observation. I think people who view a database as little more than a big spreadsheet, with each table as being a new work-sheet, will (as you say) see a table as a collection of things.

OTOH, people who view the database as a manifestation of, and consequently subservient to, their logical model (as it were) will see the table name as merely one component of the identifier they need to use to reference an actual piece of data. That is user.surname is the unique item I'm working with, but both table (user) and column (surname) must be specified.


And Dam1an - ya big woos! - of my two questions, you ignored the far more interesting one, and you applied your own practical limitations to the clearly hypothetical one and then wimped out of it entirely! I may have to stop being so agreeable with you in other threads.

[eluser]TheFuzzy0ne[/eluser]
[quote author="jedd" date="1242404778"]And Dam1an - ya big woos! - of my two questions, you ignored the far more interesting one, and you applied your own practical limitations to the clearly hypothetical one and then wimped out of it entirely! I may have to stop being so agreeable with you in other threads. [/quote]

[eluser]Dam1an[/eluser]
[quote author="jedd" date="1242404778"]And Dam1an - ya big woos! - of my two questions, you ignored the far more interesting one, and you applied your own practical limitations to the clearly hypothetical one and then wimped out of it entirely! I may have to stop being so agreeable with you in other threads. [/quote]

I do apolagise, I was doing 43 thingS at once, and in a bit of a rush

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

Yes, my table name is plural and yet my columns are singluar... you got a problem with that?
Going back to the users example... I have a table of user records, so I see the table as a collection of single items. As you (or someone) said earlier, when dealing with them, its normally one at a time (eg a foreach loop), so at that point, name is specific to that one user, and they have just the one name, not the collection of users names. Make sense?

As for the other point (although I'm sure you'll agree a case where you'll only every have 1 row for ever and ever is somewhat unlikely), I would stick with plural, purely for consistency. Otherwise I'll spend ages trying to figure out why it can't do select * from user where age > 20 (well, not ages, but I'd have to think, instead of it just coming naturally)

Who knows, maybe after this debate I will decide singular is better, but I don;t see that happening any time soon

I would really have to wander over to my book shelf (or for you guys, think books shelves, as it contains many books and also has more than one shelf in it) to check my OED grammar references ...

The book shelf is a single item, which has a collection of shelves, and each shelf has a collection of books on it, thats the way I see it

Are there any more of your points I've failed to address?

[eluser]Michael Wales[/eluser]
A little late to this post, but here's my standard:

Tables:
- All lowercase, alpha-underscore
- Pluralized name of the corresponding Model
- Every table has an id INT UNSIGNED NOT NULL auto_increment
- Many to many join tables are alphabetical order [tableA]_[tableB]
- Always one word, never abbreviate
- Strictly define constraints (unique fields should be set so in database in addition to whatever code you use)

Fields:
- All lowercase, alpha-underscore (rare cases of numbers)
- All fields are NOT NULL
- Remember UNSIGNED on appropriate INT fields
- Foreign keys are [tableA]_id (where tableA is singular)
- Multiple words separated by underscore, never abbreviate
- If a number is needed, really look at it and determine if a JOIN table is better suited
- Never call a field 'password' - that field doesn't store a password, it stores a 'password_hash'
- Never include table name (that's what dot notation and AS are for)

[eluser]TheFuzzy0ne[/eluser]
[quote author="Dam1an" date="1242405780"]The book shelf is a single item, which has a collection of shelves, and each shelf has a collection of books on it, thats the way I see it[/quote]

Quite. A book shelf is analogous to a database table. If you were to label that book shelf, would you label it "book"? I have a drawer on my tool box for screws, and it's labelled "screws", because that's what it's expected to contain. However, I don't change that label when I only have one screw.

EDIT: I guess it comes down to whether you see the table itself as an object, or a container for a collection of objects.

[eluser]Dam1an[/eluser]
[quote author="Michael Wales" date="1242406214"]
Tables:
- All lowercase, alpha-underscore
- Pluralized name of the corresponding Model
- Every table has an id INT UNSIGNED NOT NULL auto_increment
- Many to many join tables are alphabetical order [tableA]_[tableB]
- Always one word, never abbreviate
- Strictly define constraints (unique fields should be set so in database in addition to whatever code you use)
[/quote]
You just entered a very heated debate for having plural table names
Do you even have an auto incrementing ID field for a many to many table? (I just make a join primary key and thought that was normal)

[quote author="Michael Wales" date="1242406214"]
Fields:
- All lowercase, alpha-underscore (rare cases of numbers)
- All fields are NOT NULL
- Remember UNSIGNED on appropriate INT fields
- Foreign keys are [tableA]_id (where tableA is singular)
- Multiple words separated by underscore, never abbreviate
- If a number is needed, really look at it and determine if a JOIN table is better suited
- Never call a field 'password' - that field doesn't store a password, it stores a 'password_hash'
- Never include table name (that's what dot notation and AS are for)
[/quote]
You never have a null field? What about optional fields such as description?

[eluser]Michael Wales[/eluser]

Do you even have an auto incrementing ID field for a many to many table? (I just make a join primary key and thought that was normal)

If the many-to-many join table is just the two fields (keys to the tables), then no I normally won't include an auto increment id. If there are any other fields involved (most commonly a type field) - I will include a unique id.

You never have a null field? What about optional fields such as description?

I just leave them blank. It's really just a matter of preference, but I trust (bool) '' more than (bool) 'NULL'

[eluser]Dam1an[/eluser]
[quote author="Michael Wales" date="1242407746"]

You never have a null field? What about optional fields such as description?

I just leave them blank. It's really just a matter of preference, but I trust (bool) '' more than (bool) 'NULL'[/quote]

So do you set it to default to '' (blank) or do you always do that client side?