Re: Avoiding New Fields Causing Ambiguity Errors
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.