Database naming conventions

[eluser]davidbehler[/eluser]
After having worked with databases for many years now I have "developed" my own set of naming conventions that have worked well for me so far:

General:
- lowercase only
- no spaces: words and prefixes seperated by underscore
- no numbers: a to z and _ only!
Tables:
- short, unambiguous names
- singular names, no plural!
- avoid abbreviated, concatenated, or acronym-based names
- for linking tables: the names of the linked tables concatenated
Fields:
- prefix the name of every field with the name of the table
- primary key is always [table_name]_id (autoincrement)
- foreign key fields have the same name as the field they refer to
- date fields: [table_name]_date_[name]
- bool fields: [table_name]_is_[name]

Now I wanna know what naming conventions you use! Like me or totally different?
Depending how many people post their opinions I was thinking about writing about in my blog and comparing different naming conventions, maybe even adjusting mine to superior ones from someone else.

David

[eluser]Dam1an[/eluser]
General:
Exactly the same as yours
Tables:
- short, unambiguous names
- tables storing 'Objects' are plural, eg users, messages, comments etc
- avoid abbreviated, concatenated, or acronym-based names
- many to many relationship tables are [singular of table 1]_[plural of table 2] eg account_users, message_comments
Fields:
- no table name prefix, I rarely get any ambiguous fields, and if so, just do table.field, I also prefer to do $user->first_name instead of $user->user_first_name
- PK is always id (auto increment)
- FK is always [foreign table name]_id (Assuming I'm linking to the PK, which I always do)
- Booleabs, is_[name]

[eluser]jedd[/eluser]
Differences ...

Table name is never part of column name (something I'm passionately opposed to).

I tend to use SERIAL (MySQL specific) for PK's, though I'm moving away from that lately. PHP6 might see me move back to that standard. And/or if MySQL's tools start to ack that keyword (none of them seem to at the moment, which is sad/odd).

FK's are usually table_field, and though I'm not strict about this, I don't think I've ever not done it that way.

Date fields are not uniquely identified, but do tend to contain certain keywords - _when - for example.

Booleans aren't handled in a particular fashion, though I like your approach of is_ and will probably adopt that.

Oh, kudos on sticking with singular for all field names - the other thing I'm pretty passionate about is eschewing plurals in schemas. Just seems wrong, and has been an assumption that's turned me off many AR implementations.

[eluser]davidbehler[/eluser]

Table name is never part of column name (something I’m passionately opposed to).

Could you elaborate on this?
I find it pretty useful to have the table name as part of the column name as this makes sure that all my column names are unique and i can always use select * no matter how many tables I join and actually have all fields returned instead of only the last selected field of every kind of column name.

[eluser]Dam1an[/eluser]
[quote author="jedd" date="1242253171"]Oh, kudos on sticking with singular for all field names - the other thing I'm pretty passionate about is eschewing plurals in schemas. Just seems wrong, and has been an assumption that's turned me off many AR implementations.[/quote]

Just to clarify, my table name is multiple, but the field names are singular
Eg the users table is plural as it has many user objects
And a user, only has one email, so email, not emails etc

[eluser]jedd[/eluser]
Sure.

I dislike tautological nomenclature.

Context, or uniqueness, can be provided by the table.column syntax, of course, and any would-be clashes of the second component there can be resolved with the AS keyword.

So I guess it's partly the inelegance of repeating everything twice (so to speak) combined with a preference for occasionally typing AS rather than always typing an extra dozen or so characters.

[eluser]jedd[/eluser]

Eg the users table is plural as it has many user objects

But a user table with just one row would still be valid, right? Although of course not hugely useful, I ack.

And a user, only has one email, so email, not emails etc

I have about five. But I know what you mean.


Perhaps it comes down to whether you see the table name as something you use to refer to the whole table, as a collective, or as a way of accessing an entry within that table. I tend to view it as the latter, so a singular makes more sense with the way I think about tables, and with most of my use cases. I also dislike the mangling inherent in dealing with the multitude of plural variants within English.

[eluser]Dam1an[/eluser]
I see the table as a collection, hence plural
If I ever had a case where a singluar table name would seem more logical, then I would do it, as oppose to making it plural to adhere to some random naming convention (although there's something to say about consistency)

To me, doing

Code:

SELECT * FROM employee WHERE salary > 20000

just seems wrong
but if most of the time I was doing something like

Code:

SELECT * FROM user WHERE email = [email protected]

then singluar might make more sense

[eluser]NachoF[/eluser]
I use datamapper so table names are always plural... also, FKs are tablename_id.

[eluser]garycocs[/eluser]
Hi Lads,

I've been messing with DBs for years now with no solid grounding in the processes. I have managed (for the most part) to develop my rules according to your above.

I'm just wondering about linked tables and the concepts behind it.

I am using the tablename_id as the "foreign key" but that was just the way it came about.

Is there a way to actually link the tables and what are the benefits of this.

For example

My query normally looks like this

select * from city where id = $cityid

select * from pub where city_id = $cityid

etc. etc.

Are there better more efficient ways of doing this??