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

>> I prefer the singular. :) Especially when it comes to cases where
we have a table with one row. <<

Then use a singlular name and enforce this rule with a constraint:

CREATE TABLE PhysicalConstants
(lock INTEGER NOT NULL PRIMARY KEY

      DEFAULT 0
      CHECK (lock = 0),

INSERT INTO PhysicalConstants
VALUES (1, 3.141592653, ...);
>> Because the business world is fickle, that one row table may become
a multiple row table in the future. <<

Unh? Tables usually start as a set, not as a singularity.

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

You do know you are using terms from IMS and not SQL, don't you? We have referenced and referencing columns, not "master" and "details".

>> You could also consider dashes instead of underscore. <<

That does not work in SQL, only in Cobol. I am using Google to read this message and I have not scrolled down yet but I just know you will say "parent" and "child" before the end of this post.

>> I don't get too worked up about case. <<

Human beings read lowercase MUCH more accurately than all uppercase. When we had Cobol and used punch cards, it was a real problem. This is why newspaper and book don't use all uppercase. There are a ton of studies from the 1970's about readabilty of code.

>> There's some value to having each table's primary key column name
have the table name in it. It makes natural joins easier, and is useful for documentation purposes w/ foreign keys. <<

Only if it that name describes the attribute. Would you really write "Inventory_UPC" instead of just plain old, easy to understand "upc"? Again, ISO-11179 says name things for what they are, not for where they are currently located. Do you say "John_on_the_steps" and "John_in_the_hall" and so forth?

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

It is an improper abstraction, based on PHYSICALLY and not LOGICAL rules. Primary keys do not change often or quickly -- see the move from UPC to GTIN codes in retail. And I was right!! You used "child and parent" right out of IMS and network databases!! When I taught college, I could tell the language group of the students by looking at their code and their first programming language.

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

Now your age is showing! EMACS is going to scare the kids reading this <G> Why didn't you use DRI actions instead of triggers? Triggers are not portable yet in spite of Standards. They are non-relational kludges and usually slow as hell. Received on Thu Oct 10 2002 - 01:35:51 CEST