Database naming convention (yet another post of it, but a bit different)

From: Petruza <autotacle_at_uol.com.ar>
Date: 8 Oct 2002 16:59:48 -0700
Message-ID: <3e55b51a.0210081559.4ac4c1f9@posting.google.com>

Well, I've read some posts of naming conventions here in comp.databases.theory but what I want of this post is to propose my own naming convention and to see if someone can point out some concept errors or may be propose some rules I didn't.

1. tables names are in singular, not to get confused with adding or not an ending "S", and because you are refering to a kind of class from which you would handle single instances. E.G. The table refers to employees, but when you are searching for John Doe, you refer to a single employee, "employee.name = 'John Doe'", this is more readable than "employees.name = 'John Doe'"
2. tables and field names are all lower-case, I just prefer it for simplicity, and some engines or utilities may convert cases of table and field names.
3. When using a foreign key, I use as the field name, the same name of the table that the foreign key is pointing to. Example:

Table:   employee                           section
Fields:  id                      ,------->  id
         name      ,------------/           name
         section -/ (foreign key, points to field 'id' of table

'section')

Imagine that you express a query in natural language, you would say "I want to know what's joe's section, as if the section was an attribute of joe. So, using the same naming convention as for object programming, you would have the object joe and would refer to it's section as joe.section, in DBs you would refer to it as "Select employee.name from employee where employee.section = 3"

4) Use underscore to replace spaces. like_this

5) Do not abreviate! it's not at all expensive to write some letters more, and it's more descriptive and readable. Copy & Paste will allways save you the effort. example: use Mortgage_index rather than mrtg_idx, using id for identification or identificator is ok, though. ;D

6) Do not try tu use prefixes refering to data types, or identifying fields, tables, etc. It can make a mess if you have to change a field's type. Besides, you want to know the logical data of a field and not the fisical attributes, if you don't know what type of data is a field, maybe you didn't choose a descriptive name for it. example: "age" id fully descriptive, and there's no need of "intAge".

7) Do not repeat the table name in a field's name, I've seen this many times.
Example:

Table:   employee               client
fields:  employee_id            client_id
         employee_name          client_credit_card

Because when you refer to it, you can write "employee.id" and there won't be any name space problems.

Well, that's it, may be you can find more rules that would be handy? or point out why some of my rules are not usefull or incorrect? bye, thanks!

PS: Excuse my English. me not speak too good :D Received on Tue Oct 08 2002 - 18:59:48 CDT