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

From: Pablo Sanchez <pablo_at_dev.null>
Date: 9 Oct 2002 21:29:15 -0500
Message-ID: <Xns92A2D147E2240pingottpingottbah@209.189.89.243>

71062.1056_at_compuserve.com (--CELKO--) wrote in news:c0d87ec0.0210091535.5ad009a6_at_posting.google.com:

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

Thanks but no thanks. :)

Seriously, if I may direct your attention to An Introduction to Database Systems (3rd edition - yeah I'm that old! :), page 88 Date uses his [supplier]/[part] example. Not [suppliers], [supplier] On page 85, he talks about [part], not [parts].

Take it up with him! <g>

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

Not necessarily always. For instance, there might be a table called [site_config] that has a single row.

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

I suppose I wasn't clear enough in my example, I was referring to the "relationship" between the [master] and the [detail]

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

You've munged the citing, I didn't write that. Use a real news reader! heh heh!

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

Again, I didn't write the above either... I'm clipping all the parts of your response that I didn't write ... :)

fyi: oracle uppercases all column names and Sybase ASE respects case.

I personally prefer all lowercase for column names and reserved words in uppercase.

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

That's correct that it's based on physical rules. Is it improper? I disagree completely. The important thing about the abstraction is that it considers the entire business problem at hand. I'm in the business of delivering high-performing and _maintainable_ systems.

We need to ensure that the application isn't a stove-pipe application.
We are trying to avoid that anti-pattern. With that, we build the abstraction so that the business can change quickly and the application can react quickly as well.

> Primary keys do not change often or quickly -- see the
> move from UPC to GTIN codes in retail.

The fact is that primary keys _do_ change and the amount of re-work involved to fix the damage can be significant and extremely costly. I
submit that building the abstraction with surrogate keys and using the
business 'primary keys' as natural keys has a lot of gain with no loss
of representation.

> And I was right!! You used
> "child and parent" right out of IMS and network databases!!

Ugh, nooooooooooooo.... just the names I happened to pick. As you well know, a relational database can represent a network and/or hierarchical database therefore it's not surprising to see those names
used. Well, not to me at least. <g>

> When I taught college, I could tell the language group of the
> students by looking at their code and their first programming
> language.

Sorry, you missed the boat. I never wrote anything in IMS. <g>

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

I suppose so... <chuckle> Any Unix person worth their salt knows emacs... no? :)

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

Please provide me with factual data that triggers, let's pick Sybase ASE, are 'slow as hell.' I don't think you can, I think you're wrong.

Provide me the code and the benchmark that you use to prove your assertion. Also, let's define slow as hell: 30% slower? 50%?

Thx!

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

Received on Wed Oct 09 2002 - 21:29:15 CDT