Re: Love or hate, or? domains with cardinality two

On 2015-11-03 22:17:41 +0000, -CELKO- said:

> I did a piece on this topic:
>
> https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=celko%20bit%20of%20a%20problem
>
>
> I am against it for reasons given there.

The product release example is very similar to the Employee example. Yes, enforcing the relevant constraints on the master_release_flg is a pain in the neck: I wouldn't know how to do it declaratively, unless there is support for subselects in constraint-check clauses (which is not widespread, as far as I know), and even then I would not rule out triggers because they might perform better. Partial unique indexes might help, too, if available.

However, defining a master release table would be better, in my opinion, than your proposed solution based on the minimum release number, because the meaning would be clearer than by using an implicit rule, which amounts to not enforcing the constraint (is the product with the minimum release number really the master release, or has someone deleted the master release by mistake?):

create table Master_release (
  product_id char(15),
  product_release_nbr integer,
  primary key (product_id),
  foreign key (product_id, product_release_nbr) references Products    on update cascade on delete restrict
);

In this case, the only non-trivial constraint is to ensure that each product (with at least two releases) has a corresponding master release. Unless I am missing something, I believe that this solution would be easier to implement than the one based on a master_release_flg flag, and not much more complicated than your proposed solution (with the advantage that the view with the master release of the products becomes trivial).

Your last example about the blood factor has made me realize that there is a semantic nuance between a proposition and its negation, and two (positive) propositions that are mutually exclusive. Even if the only values for the blood factor are Rh+ and Rh-, certainly I would want to assert both facts: I wouldn't read a tuple (1,'Rh-') as "Person 1 does not have blood factor Rh+", nor would I design a database with schemas Person(id) and Rh_Positive(id) and retrieve people with Rh- blood factor using "Person minus Rh_Positive"...

Nicola

• news://freenews.netfront.net/ - complaints: news_at_netfront.net ---