Re: Select Statement with 4 tables help required please

From: David Browne (meat_at_hotmail.com)
Date: 08/12/04 

Date: Thu, 12 Aug 2004 10:14:35 -0500

"Joe Celko" <jcelko212@earthlink.net> wrote in message
news:O0W9tJAgEHA.3928@TK2MSFTNGP11.phx.gbl...
> >> No. Singular names for relations is not only common, but correct. <<
>
> Correct based on what ANSI or ISO standard? It is in violation of
> ISO-11179 and common sense.
>
> Quick test #1: Look at the data element name "employee" and tell me on
> sight if it is a table or a column with your naming conventions. It is
> legal SQL syntax to use the same name for both a table and a scalar.

That's a good thing. It would be a column if it was the domain
participating in another relation, and a table name if it was an enumeration
of that domain.

>
> The worst thing that OO people trying to learn SQL do is use the same
> name for a table and a column. It is legal SQL syntax, but a mess to
> read.
>
"A mess to read" is not an argument. I say it's not a mess to read.
Complete impass.

> Quick test #2: Close your eyes and let someone say "employee" and a
> picture of Dilbert pops into your head -- a single incidence of a
> concrete entity. Close your eyes and let someone say "Personnel" and
> yuou get an abstract image of a class in a business organization. Big
> conceptual difference.
>

And where should we enumerate all the employees? In the employee table.

> >> Huh? He said "IPCRecommendation.RecommendationID = Recommendation.ID"
> <<
>
> Those are full table names and a bitch to read.

Again. Not a bitch to read.

>An alias would look
> like this: "R1.recommendation_id = IPC.recommendation_id" and the same
> data element name would be used in the entire schema. Why? Because it
> is the same data element in the entire schema.

Why not? because it gives no information about the relationship.

  IPCRecommendation.RecommendationID = Recommendation.ID

makes it clear that this is a foreign key relationship and that
recommendation is the primary key table.

>
> >> A table named, say appointment might have a column, date. The proper
> name for the column is "DATE" not "APPOINTMENT_DATE", which would be
> redundant. <<
>
> Wrong, totally, horribly wrong! First, never use a rserved word for a
> column name. DATE is also too vague even if you changed it to "date";
> it begs the question "date of what?" appointment? birth? hire?
> termination? death?

Obviously the date of the appointment. If an appointment had more than one
date, then another name would be required.

>
> This is the old joke:
>
> "When I was a kid, we had three cats."
> "what were their names?"
> "Cat, cat and cat."
> "That sounds screwed up; how did you tell them apart?"
> "Who cares? They did not come when you called them anyway!"
>
> Again, read ISO-11179. Your data will not come whe it is called either.
>
> >> But then if that column is referenced by another table is would be
> referenced as "APPOINTMENT_DATE". <<
>
> And if it appears in ten tables, you blindly construct ten new name and
> screw your data dictionary beyond repair. One hundred tables, one
> hundred new names! As you walk from room to room in your house, do also
> you change your name, based on your physical location?

Nonsense. Look, I have a livingroom television and a bedroom television.
If I am in the living room I don't say "honey turn off the livingroom
television". In the livingroom it's just the television. But as we pull
out of the driveway I say "honey I left the livingroom television on". The
name is qualified when refered to from outside of its local namespace,
unqualified from within.

>
> >> Similarly a table called recomendation should not have a primary key
> of recommendation_id. It should be just id.<<
>
> And when I look up this mystic, magic, all-purpose "id" in the data
> dictionary, I can quickly see that it appears in hundreds of tables and
> views, and that it has tens of various datatypes. Why would you find
> this desirable?

Why would you not? What's the problem? It's obviously an artificial key
and it's obvious what table it belongs to.

> >> It is a basic rule of naming that you shouldn't repeat higher levels
> of the namespace in lower level names without a very good reason.
> Column names are scoped inside table names, and so shouldn't repeat the
> name of the table. <<
>
> NO, the data elements exist at the schema level in an RDBMS!! I put
> tables together from attributes (with the help of a data dictionary) to
> models model of entities in SQL.

No you don't and no one else does either. The schema is made up of tables
which are in turn made up of columns. No one ever has to go directly from
the schema to the column.

>
> You can do joins because of this ability to build a table from those
> data elements at the schema level
>
> In COBOL and traditional file systems, I have separate, disjoint files
> as my unit of work. That is where we get the rules about not repeating
> names in the unbreakable hierarchy of records and fields. I had to
> write them out with full qualification, just like you are still doing in
> SQL. This is an RDBMS and not a COBOL file system naming hierarchy and
> records and fields are nothing like rows and columns.

Columns names are scoped inside table names. Table names are scoped inside
schema names, and schema names are scoped inside database names. Nothing
like COBOL. Not a hangover from file processing.

>
> I am trapped in an extended stay hotel for another week while I wait for
> the movers,

For that, I am truly sorry.

>so I started to pool notes for a book on SQL programming
> style together. I spent a few years doing this kind of research for
> AIRMICS in the 1980's and I am amazed as to how much of that work is
> ignored today.

Yes I too am appalled at the bad and slopy SQL style I see every day.

David

Relevant Pages

  • Re: How do i write Set based queries and avoid a cursor?
    ... We did that stuff with punch cards so we could slip in more cards ... somewhere in the schema. ... Learn to write good Standard SQL. ... The data element names change from the rest of the schema, ...
    (comp.databases.ms-sqlserver)
  • Re: Select Statement with 4 tables help required please
    ... The worst thing that OO people trying to learn SQL do is use the same ... data element name would be used in the entire schema. ... "Cat, cat and cat." ...
    (microsoft.public.sqlserver.programming)
  • SQL Adapter Disassembling Result Sets & Dynamic Parameters
    ... properties of the schema returned in my orchestration. ... Also after processing all the records returned by SQL adapter in my ... from the toolbox drag an xml disassembler ...
    (microsoft.public.biztalk.general)
  • SQL Adapter Disassembling Result Sets & Dynamic Parameters
    ... properties of the schema returned in my orchestration. ... Also after processing all the records returned by SQL adapter in my ... from the toolbox drag an xml disassembler ...
    (microsoft.public.biztalk.server)
  • Re: DB Architecture Questions (for joe celko)
    ... No. Learn the ISO-11179 rules for data element names, ... Does your industry actually have "product_id" as a standard? ... After that, there is a natural key in the data, such as (longitude, ... That is why SQL has UPDATE, DRI actions and ALTER statements; ...
    (microsoft.public.sqlserver.programming)