Re: design question

From: JOG <jog_at_cs.nott.ac.uk>
Date: Fri, 17 Oct 2008 03:32:23 -0700 (PDT)
Message-ID: <b67027fa-c98c-4183-a2a7-cabf8870dd56_at_y29g2000hsf.googlegroups.com>


On Oct 17, 9:26 am, robu..._at_gmail.com wrote:
> > 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”.

Not sure that is a concern - just read the external predicate for PARTS as being "p is a possible part of product_type t". It doesn't require that parts should exist seperately.

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

In that case the answer is declarative constraints. RM is just math. A relvar has a set of possible relation instances as its domain. The stated predicate of any relvar hence defines the intension of that set of relations, and constraints may be viewed as being part of that intension, defining what is possible.

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

There's probably a reason for that ;) Regards, J. Received on Fri Oct 17 2008 - 12:32:23 CEST

Original text of this message