Database naming conventions

[eluser]tekhneek[/eluser]
A little late but who cares, this is how I handle my "database" (I'd usually call them databases, but pluralization is apparently the devils.)

Table naming conventions
- Plural (e.g. "users")
- 1:* relationships are: "table1_table2" (e.g. "users_categories" maintaining pluralization throughout.)

I don't use the alphabetical approach because "categories_users" sounds confusing to me where as "users_categories" sounds logical and you can immediately understand the significance (at least I can) of that naming convention.

You could say "users have categories/categories have users"... I mean does the dog wag the tail or does the tail wag the dog?

- *:* relationships are spread over 3 tables (almost always)
Table 1: photos
Table 2: photo_galleries
Table 3: photo_galleries_photos

Column conventions
- Always lowercase
- alpha-underscore
- Primary keys are always first -- "id" (e.g. users.id)
- Foreign keys are always fk_table_id (e.g. fk_user_id) following any primary id. (Pluralization is stripped to signify a single relationship.)
- Last column is always "created" datetime/timestamp regardless of table significance albeit joining, primary, secondary, tertiary, etc.

photo_galleries_photos example
fk_photo_galleries_id
fk_photo_id
created

That's my two cents.