Re: design question

> PARTS:
> part,
> product_type, ...
> PRIMARY KEY(part, product_type)

Actually parts cannot exist by themselves, that’s why there is a composite key {product, part} in “product_parts”. The actual data is about virtual software devices that can exist only if explicitly created on a specific instance of a “product”. Something like the “eth0”, “eth1”, “hdd0” devices in your computer. You cannot know their serial number and you don’t care about their physical existence outside the computer (product). You just need to know about their existence inside the computer (product) and their functional state.

> COMPONENTS:
> product,
> part, ...
> PRIMARY KEY(product, part),
> CHECK(
> SELECT 1 FROM PRODUCTS, PARTS
> WHERE PRODUCTS.product_type = PARTS.product_type
> AND PRODUCTS.product = COMPONENTS.product
> AND PARTS.part = COMPONENTS.part
> )
>
> Few things. It is pseudocode obviously, and it also assumes that your
> RDBMS will resolve a check to false if it returns an empty relation,
> and true otherwise.

Well, the only declarative option is foreign key, because the DBMS does not support constraints. The other variant is using triggers. Anyway, I’ am not looking for an implementation detail, it is more a philosophical problem.

This design was created with DKNF (Domain-Key Normal Form) in mind. Perhaps we should move the discussion in more theoretical areas, such as using foreign references on composite keys, or sacrificing functional dependencies in favour of DKNF.

Looking at “product_parts” relation we can see that there is a partial dependence on the composite key , “product” -> “product_type”. However if we think about {product_type, part_type} as a whole (since is a reference to a composite key), it may be said that {product, part} -> {product_type, part_type}. But still not satisfying 2NF definition.

Using foreign references on composite keys may eventually lead to situations like this. In this example, the variant of using a separate relation for part_type (just a dumb list of part_type names) just for using a foreign key to a non-composite key is artificial since no “part_type” has any significance outside a context of a “product_type”. So, should we refrain on using foreign references on composite keys and rely on other types of constraints instead?

Or sould we go for alternative design methods such as DKNF? Unfortunately there is very little material about DKNF (I first heard about DKNF about one year ago in a discussion on this group), and haven’t seen many designs using it. Received on Fri Oct 17 2008 - 10:26:03 CEST