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

"Marshall Spight" <mspight_at_dnai.com> wrote in news:AJTo9.92754$DN4.13763_at_sccrnsc01:

> "Petruza" <autotacle_at_uol.com.ar> wrote in message
> news:3e55b51a.0210081559.4ac4c1f9_at_posting.google.com...

>>
>> 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.

FWIW, I prefer the singular. :) Especially when it comes to cases where we have a table with one row. Because the business world is fickle, that one row table may become a multiple row table in the future.

Additionally, when I validate the model, we talk about a single row against the cardinality:

[master] ----> [detail]

   (0,N) (1,1)

A [master] may or may not have a [detail] A [detail] has at least one and at most one [master]

>

>> 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.
>> 4) Use underscore to replace spaces. like_this

In my opinion, case is only relevant when the RDBMS supports mixed case and we start getting zany names:

    id
    Id
    ID

this develops into an anti-pattern for the developers.

>
>

>> 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:

Here's what I prefer doing:

   [master]

      id
      col1
      col2
      colN

   [detail]
      id
      col1
      col2
      colN
      master_id

Using the above naming convention, where all tables get a surrogate key (builds a proper abstraction between implementation and the business side changing its definition of a primary key!) and the children tables building the FK as: parent + "primary column name", developers can _easily_ code up their SQL.

Additionally, if one is building triggers, it's very easy to write an EMACS keyboard macro to write the trigger code. I did this for a client and built RI for 20 tables in 15 minutes. Bug free! :)

Thoughts?

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com