Attribute name prefixes, domains, joins, ISO 11179

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"
2) The table has the prefix tbl..., exposing the storage method to the interface
3) The attributes have three letter prefixes, same problem as (2). 4) The intProductId column looks like an unnecessary surrogate key

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

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? 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?

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? To put it another way, if name + type implies 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 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 - 03:23:44 CET