Re: Attribute name prefixes, domains, joins, ISO 11179

archon_at_quantumfire.com wrote:
> Some thoughts on attribute names.
> A common response to inexperienced database developers who post DDL or
> pseudo-DDL is that they have mixed up the physical model with the
> logical model, or the logical and conceptual model, because they have
> prefixed their tables with, eg, "tbl", and sometimes their column names
> with, eg "int", "str", "dat", referring to the low level type (as
> opposed to a representation term - which would also go at the end and
> not the start according to 11179)

Grrr.
http://www.iso.org/iso/en/CatalogueDetailPage.CatalogueDetail?CSNUMBER=35347&ICS1=35&ICS2=40&ICS3=

87 Suiss franks - is the naming part freely available somewhere?

> If you'll excuse a little descriptive shorthand, an example of "getting
> it wrong" might be this:
>
> table tblProduct(
> intProductId int not null primary key,
> strProductCode char(4) not null unique,
> strProductName varchar(32) not null,
> datProductCreatedDate datetime not null default current_timestamp)
>
> First of all, what are the identifiable mistakes here?
> 1) The table is named in the sigular. Probably better to use "Products"
> than "Product"

TIMTOWDI. The argument for naming the table using a plural form is that it is the table you are naming, not the individual rows, and the table will mostly hold more than one row. OTOH, the name will also be used in contexts where one row is meant. Product.Name reads easier than Products.Name when we are using one value.

Some use plural names for tables, some use singular names for tables, some use both forms mixed, some allways use both by means of synonyms. The 'mixed' approach is cumbersome - allways check which form is used - but there are many systems where it is used, it doesn't prevent the sytem from functioning.

> 2) The table has the prefix tbl..., exposing the storage method to the
> interface

Nitpicking: a table is data representation method, not a storage method.
It just looks like a storage method from an applications point of view, because the strorage method is hidden from the application.

> 3) The attributes have three letter prefixes, same problem as (2).

exposing the type / domain.

> 4) The intProductId column looks like an unnecessary surrogate key

Let's not go into this now - it is a pet topic for many here, the discussion would overwhelm the other points you raise.

> The table does include 11179 representative terms: "id", "code", "name"
> and, I would argue, "date". The fact that date is also a low level type
> does not preclude it from also being a representative term. Although
> the representative term should probably be "DateTime" if we really care
> about what the tiem component is. If we don't, then we should probably
> have a constraint that productcreateddate =
> round_time_to_zero(productcreateddate).
>
> OK, so lets fix up the original definition a bit...
>
> table Products(
> ProductCode char(4) not null primary key,
> ProductName varchar(32) not null,
> ProductCreatedDate datetime not null check no_time_component())
>
> and let's also bring in a second table, Warehouses. At some point,
> products and warehouses are probably going to be related, but for the
> purpose of this post, that is irrelevent.
>
> table Warehouses(
> WarehouseCode char(4) not null primary key,
> WarehouseName varchar(32) not null,
> WarehouseCreatedDate datetime not null check no_time_component())
>
> OK, now what domains can we identify here? To put that another way, if
> this DBMS had full support for creating user defined types as domains,
> which ones would be created?
>
> The obvious first list, using a D prefix to distinguish between the
> domains and the attributes themselves, is...
> DProductCode, DProductName, DProductCreatedDate, DWarehouseCode,
> DWarehouseName, DWarehouseCreatedDate

The type/domain doesn't care about the context - a value is either in the defined set or not.
The domain/type names can/should only reflect the defined set of values. In these names there is context.

> Let's look at this list more thoroughly. It seems intuitive to me at
> least that product codes and warehouse codes are not the same kind of
> thing, The codes may have rules as to how they are built, so product
> codes might be all numeric, warehouse codes might be derived somehow
> from their location, or whatever. It certainly seems like we have 2
> different domains there.
> What about DProductName and DWarehouseName? In my opinion this one is a
> bit nebulous. Both being composed of arbirtrarily created names, they
> certainly cannot be checked against a validity pattern. But we might
> say the fact that they are both varchar(32) is just a coincidence.
> Perhaps the longest product name is can really only be 20 letters,
> whereas a warehouse name in some strange country might be 80 letters!
> These are probably, but less certainly than with codes, 2 different
> domains.
> Ah, but what about DProductCreatedDate and DWarehouseCreatedDate? Both
> of these are dates without time components that represent when the DBMS
> first learned of the existence of that particular product code or
> warehouse code. In fact, the created date is an attribute that could,
> if we thought it was important, be part of the heading of every relvar
> in the system.
> So do we actually only have one domain here, which might be called
> DCreatedDate?

DCreatedDate still holds context, so it assiociates with something different than just the set of values. It doesn't name the domain, it names something else.

> Well, if that is the case, then we might need to take a
> look at the attribute names as well. The question is, should we call
> the attribute of type DCreatedDate in the Product table by the name
> "ProductCreatedDate" or should we call it just "CreatedDate"? We
> already know it is a property of a Product, because it is in the
> products table. And there is nothing special about the attribute domain
> that says it can only belong to the Products table: everything can and
> indeed must have a date at which the system first became aware of it.
> By way of contrast, ProductCode and WarehouseCode could not simply be
> called "Code", because they represent two very different kinds of
> "Code". There seems to be no good reason to prefix the CreatedDate
> attribute with the table name of which it is part. Doing so almost
> seems like prefixing the name of the products table with "tbl". But
> wait... if both Product and Warehouse contain an attribute of the same
> type - "DCreatedDate" - and with the same name - "CreatedDate" - then
> doesn't that suggest these two attributes would form the basis of a
> logical relationship? Does it suggest that we could join the two tables
> together on this column and get a "meaningful" answer? And if it does
> suggest that, well, is that actually the case?

Both the type and the attribute have a name here. Product.Name could be of type ProductName.

> Using this reasoning I eventually find myself struggling with no good
> answer to the following: Should all attributes of all tables be
> prefixed with the name of the table of which they are part?

TIMTOWDI again. Table name prefixes are very common, even prescribed by many coding standards, but they are overqualification.

> To put it
> another way, if name + type implies domain,

What, to you, is the difference between type and domain?

> then Is it possible to have
> attributes of the same domain in different relations and not be able to
> make a meaningful join those shared attributes?
>
> This question does not refer to those attributes which form part of a
> foreign key, because these will certainly be named the same in the
> referencing table as they are in the referenced table

Why?

The name of the foreign key should reflect a role.

Person:
Person.Name PersonName.

Game:

Game.Black PersonName references Person,
Game.White PersonName references Person,
Game.Arbiter PersonName references Person.

> if we use the
> above reasoning, and are therefore precluded from automatically getting
> their table name as part of the attribute name (ie, you can obviously
> make a meaningful join on those attributes)
>
Received on Fri Nov 25 2005 - 12:51:30 CET