Re: Avoiding New Fields Causing Ambiguity Errors

From: Topmind <topmind_at_technologist.com>
Date: 13 May 2002 09:59:35 -0700
Message-ID: <4e705869.0205130859.1ce01517_at_posting.google.com>


71062.1056_at_compuserve.com (--CELKO--) wrote in message news:<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.

But this is a significant source of "ambigious" error messages in many DB engines. (It could be eliminated if the DB used ER structures to know they are the same and not complain, but many don't.)
I put reducing practical headaches above violating idealistic concepts. If somebody gets used to such convention, then they know what "_ref" means anyhow.

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

Actually, you may need a lot of qualifiers for that last line, no? Example:

   ....
   ON A.x = B.x) AS AB(A.a,B.b,A.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.

Thanks for your feedback, -T- Received on Mon May 13 2002 - 18:59:35 CEST

Original text of this message