Re: design question

On Oct 17, 12:06 pm, robu..._at_gmail.com wrote:
> JOG wrote:
> > 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.
>
> Agree, but not practical. It means to populate the relation with all
> possible values (for example “eth0”, “eth1”, … “eth9999”).

No it doesn't. It means they have to be there when you add a product that possesses them, no different to the composite key hack. I appreciate, however, that not many commercial dbms offer anything but the most basic constraint support.

> If the
> parts aren’t known apriori is better to add them when known and in the
> context they apear (as part of a composite key).
>
>
>
> > > 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.
>
> Well, a foreign key is a kind of poor-man’s constraint in absence of
> other declarative support. So, we end up with guess what? A
> combination between DKNF and (partially broken) normal forms.
>
> I hate to choose between two evils. And unfortunately this is often
> the case in implementing relational databases with pseudo-relational
> DBMSs.
Received on Fri Oct 17 2008 - 17:12:05 CEST