Re: Avoiding New Fields Causing Ambiguity Errors

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 10 May 2002 07:44:40 -0700
Message-ID: <c0d87ec0.0205100644.13b861ef_at_posting.google.com>


>> 1. Different entities happen to have the same kind of data. Common
examples include Title and Status. Almost any entity you can think of may have a title
or status field (or synonyms like "name", "description", etc.) <<

Okay, I understand qualifiying these general words with an additional word or two like "invoice_status" because they do NOT have the same kind of data element -- I cannot do the same things with "invoice_status" as I can with "order_shipping_status". Look up ISO 11179 Metadata specs on creating data element names; good stuff.

>> 2. Key fields. I like the convention of "x_ref" for foriegn keys
and "x_id" for the primary key. <<

ARRRGHHH! Stop that at once! You are telling me what the column does in this particular table and not what it means in the logical data model.

>> 3. Bad normalization (duplication). Don't blame me, I just
inherited the tables, I didn't make them. Vendors cannot always assume that table designers are sober. <<

Okay, I'll give you this one ...

>> [My personal convention is a one or two letter abbreivation
followed by an integer] For table aliases or column names or column aliases? <<

Just for tables:

 SELECT emp_name, dept_nbr
   FROM Personnel AS P1
  WHERE ..
    AND EXISTS

        (SELECT *
           FROM Personnel AS P2
          WHERE ..);

The digit gives me an idea of how often a table is used in the statement. I hate people who use (a,b,c,d, ...) as alias names -- that tells you nothing as you read the code.

I use the AS operator everywhere. I think it makes code easier to understand. When I give an alias to a table expression, I usually give explicit column names:

 (TableA AS A
  LEFT OUTER JOIN
  TableB AS B
  ON A.x = B.x) AS AB(a,b,c,..)

I use column aliases mostly for expressions, but otherwise I look for the role they play in the result set, as in

 SELECT P1.emp_name AS employee, P2.emp_name AS boss, ..    FROM Personnel AS P1, Personnel AS P2   WHERE .. Uppercase all keywords, capitalize schema objects, lowercase scalars. One clause per line, with subexpressions aligned on gutters. Received on Fri May 10 2002 - 16:44:40 CEST

Original text of this message