design question

On Oct 15, 6:48 am, wrote:
> I would appreciate your advice about the design of the following
> schema:
> We have a database of products and we know that:
>  - a product is of a certain type
>  - a product has several parts
>  - a product part can belong only to products of some specific type
>
> So we end up with:
>
> products {product, product_type, ...}
>  key: {product}
>
> part_types {product_type, part_type, ...}
>  key: {product_type, part_type}
>
> product_parts {product, part, product_type, part_type, ...}
>  key: {product, part}
>  foreign keys: products {product, product_type}, part_types
> {product_type, part_type}
>
> The problem is the “product_type” attribute in relation “products”.
> While redundant because it was already stated that the product is of a
> certain type in the “products” relation, it is required as part of the
> foreign key to “part_types”.  Still anomalies are avoided due the
> foreign key to “products relation” (not to the primary key but to a
> superkey).
>
> The obvious solution is removal of “product_type” from “product_parts
> “ and addition of a constraint to check if a product type can be
> associated with a certain part type. However it is more difficult to
> implement in a SQL database than a foreign key.
>
> The design looks awkward, it is more complicated, adds a lot of
> redundant information, but it makes easy to enforce some constraints
> that would need to be implemented using triggers (this is actually a
> simplified version of the schema, in the real database there are more
> tables that are using overlapping foreign key to “products” table).
>
> What do you think as being more important, sticking to normalization
> rules or choosing a compromise that will add some redundant data but
> under a strict control using foreign keys? Can you spot advantages/
> disadvantages of each variant? Or perhaps you could suggest a better
> solution…
>
> Thank you for your time

Since you give no example data it is not entirely clear what you want.
Assuming PARTS have a single type, then I see only three tables:
Products, Types, and Parts modeling these rules:
* Products have a type
* Parts have a type
* Parts belong to Products of the same type.
* Types have parts.

Products
product (PK)
type (FK to Types)
other

Types
type (PK)
other

Parts
partcode PK
type
product
other
(type & product FK to Products)

Now if parts can belong to more than one product type, then perhaps a
fourth table is needed. Or expand the PK of the Parts table to be
partcode & type.

Basically I think you need to rethink your starting data model. but I
hope this helps.
 >> Stay informed about: design question 

Ed Prochak wrote:
> On Oct 15, 6:48 am, wrote:
> > I would appreciate your advice about the design of the following
> > schema:
> > We have a database of products and we know that:
> > - a product is of a certain type
> > - a product has several parts
> > - a product part can belong only to products of some specific type
> >
> > So we end up with:
> >
> > products {product, product_type, ...}
> > key: {product}
> >
> > part_types {product_type, part_type, ...}
> > key: {product_type, part_type}
> >
> > product_parts {product, part, product_type, part_type, ...}
> > key: {product, part}
> > foreign keys: products {product, product_type}, part_types
> > {product_type, part_type}
> >
snipped

> Since you give no example data it is not entirely clear what you want.
> Assuming PARTS have a single type, then I see only three tables:
> Products, Types, and Parts modeling these rules:
> * Products have a type
> * Parts have a type
> * Parts belong to Products of the same type.
> * Types have parts.


Unfortunateley not. There is a product type (or category if you
prefer), and part type. A part of a certain type can belong only to
some product types (categories).

This sample data hopefully would make things clearer:

products
product, product_type:
pc A, computer
pc B, computer
lp A, laptop
srv A, server

part_types
product_type, part_type:
computer, network card
computer, graphic card
laptop, network card
laptop, battery
server, hd array
server, network card

product_parts
product, part, product_type, part_type:
pc A, netcard 1, computer, network card
pc A, video card, computer, graphic card
pc B, netcard 1, computer, network card
srv A, netcard 1, server, network card
srv A, netcard 2, server, network card




> Basically I think you need to rethink your starting data model. but I
> hope this helps.


Of course, we can remodel like this:

part_types {part_type}
key: {part_type}
(just a list of valid part type names)

product_types_part_types {product_type, part_type, ...}
key: {product_type, part_type}
(valid combinations of product type - part type)

product_parts {product, part, part_type}
key: {product, part}
foreign key: products {product }
foreign key: part_types {part_type}
(parts that belong to a product)

However, there is no way to prevent occurrence of wrong combination of
"product_type" – "part_type" in the “product_parts”.

That is my problem, having to choose between a design that looks
denormalized but which can enforce a constraint, and a normalized
design that does not prevent invalid data to occur (and relly on
triggers to enforce integrity).
 >> Stay informed about: design question 

On Oct 16, 5:54 am, wrote:
> Ed Prochak wrote:
> > On Oct 15, 6:48 am, wrote:
> > > I would appreciate your advice about the design of the following
> > > schema:
> > > We have a database of products and we know that:
> > > - a product is of a certain type
> > > - a product has several parts
> > > - a product part can belong only to products of some specific type
>
> > > So we end up with:
>
> > > products {product, product_type, ...}
> > >  key: {product}
>
> > > part_types {product_type, part_type, ...}
> > >  key: {product_type, part_type}
>
> > > product_parts {product, part, product_type, part_type, ...}
> > >  key: {product, part}
> > >  foreign keys: products {product, product_type}, part_types
> > >  {product_type, part_type}
>
> snipped
>
> > Since you give no example data it is not entirely clear what you want.
> > Assuming PARTS have a single type, then I see only three tables:
> > Products, Types, and Parts modeling these rules:
> > * Products have a type
> > * Parts have a type
> > * Parts belong to Products of the same type.
> > * Types have parts.
>
> Unfortunateley not. There is a product type (or category if you
> prefer), and part type. A part of a certain type can belong only to
> some product types (categories).
>
> This sample data hopefully would make things clearer:
>
> products
> product, product_type:
>  pc A, computer
>  pc B, computer
>  lp A, laptop
>  srv A, server
>
> part_types
> product_type, part_type:
>  computer, network card
>  computer, graphic card
>  laptop, network card
>  laptop, battery
>  server, hd array
>  server, network card
>
> product_parts
> product, part, product_type, part_type:
>  pc A, netcard 1, computer, network card
>  pc A, video card, computer, graphic card
>  pc B, netcard 1, computer, network card
>  srv A, netcard 1, server, network card
>  srv A, netcard 2, server, network card
>
> > Basically I think you need to rethink your starting data model. but I
> > hope this helps.
>
> Of course, we can remodel like this:
>
> part_types {part_type}
>  key: {part_type}
>  (just a list of valid part type names)
>
> product_types_part_types {product_type, part_type, ...}
>  key: {product_type, part_type}
>  (valid combinations of product type - part type)
>
> product_parts {product, part, part_type}
>  key: {product, part}
>  foreign key: products {product }
>  foreign key: part_types {part_type}
>  (parts that belong to a product)
>
> However, there is no way to prevent occurrence of wrong combination of
> "product_type" – "part_type" in the “product_parts”.
>
> That is my problem, having to choose between a design that looks
> denormalized but which can enforce a constraint, and a normalized
> design that does not prevent invalid data to occur (and relly on
> triggers to enforce integrity).

Then you need entities/relations to model that requirement. Think
about the invalid case. What relations can model the allowed states
such that you cannot make the invalid one (hint: PART_TYPES). For
example a battery cannot go into a server. So what constraint can you
put on PRODUCT_PARTS to make inserting:
srv A, battery1, laptop, battery
fail?
Which product type does laptop refer to in this case?
Think about it.
Ed
 >> Stay informed about: design question 

Off the top of my head, I would go with some declarative constraint.
We really shouldn't need triggers to do this sort of thing. Given your
problem description:

> We have a database of products and we know that:
> - a product is of a certain type
> - a product has several parts
> - a product part can belong only to products of some specific type

This infers to me a schema of the nature:

PRODUCTS:
product,
product_type, ...
PRIMARY KEY(product)

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

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.
Regards, Jim.
 >> Stay informed about: 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.
 >> Stay informed about: design question 

wrote:
> What do you think as being more important, sticking to normalization
> rules or choosing a compromise that will add some redundant data but
> under a strict control using foreign keys? Can you spot advantages/
> disadvantages of each variant? Or perhaps you could suggest a better
> solution…

In general: Declarative constraints on fully normalised relvars is the
best solution. If your DBMS doesn't support multi-relvar constraints
other than foreign keys (and this is the case for most SQL DBMSs), my
personal opinion is that it is better to denormalise and use foreign
superkeys, than to use procedural triggers---as long as you make sure
all anomalies (except the inevitable redundancy) usually associated with
denormalisation are eliminated by the foreign superkey(s).

In other words, I think your solution is fine.
--
Jon
 >> Stay informed about: design question 

On Oct 17, 9:26 am, 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.
 >> Stay informed about: design question 

JOG wrote:
> On Oct 17, 9:26 am, 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�). 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.
 >> Stay informed about: design question 

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

DKNF is useless. Ridiculously so.
--
Jon
 >> Stay informed about: design question 

On Oct 15, 6:48 am, wrote:
> I would appreciate your advice about the design of the following
> schema:
> We have a database of products and we know that:
>  - a product is of a certain type
>  - a product has several parts
>  - a product part can belong only to products of some specific type
>
> So we end up with:
>
> products {product, product_type, ...}
>  key: {product}
>
> part_types {product_type, part_type, ...}
>  key: {product_type, part_type}
>
> product_parts {product, part, product_type, part_type, ...}
>  key: {product, part}
>  foreign keys: products {product, product_type}, part_types
> {product_type, part_type}
>
> The problem is the “product_type” attribute in relation “products”.
> While redundant because it was already stated that the product is of a
> certain type in the “products” relation, it is required as part of the
> foreign key to “part_types”.  Still anomalies are avoided due the
> foreign key to “products relation” (not to the primary key but to a
> superkey).
>
> The obvious solution is removal of “product_type” from “product_parts
> “ and addition of a constraint to check if a product type can be
> associated with a certain part type. However it is more difficult to
> implement in a SQL database than a foreign key.
>
> The design looks awkward, it is more complicated, adds a lot of
> redundant information, but it makes easy to enforce some constraints
> that would need to be implemented using triggers (this is actually a
> simplified version of the schema, in the real database there are more
> tables that are using overlapping foreign key to “products” table).
>
> What do you think as being more important, sticking to normalization
> rules or choosing a compromise that will add some redundant data but
> under a strict control using foreign keys? Can you spot advantages/
> disadvantages of each variant? Or perhaps you could suggest a better
> solution…
>
> Thank you for your time

Please excuse the self-promotion, but this looks like an example of a
design case that I tackle in an article here:
http://www.sqlservercentral.com/articles/Data+Modeling/61528/

I am in agreement with Jon that one should favor controlled redundancy
for the ability to implement the constraint declaratively.

HTH,
TroyK
 >> Stay informed about: design question 

Yes, it is the same situation as in your article. Good article indeed,
I will keep it as reference since I have hard-times in convincing
others that controlled redundancy is preferable to a high risk of
having inconsistencies in the database. Well I have also troubles in
explaining them why I prefer to use composite foreign keys instead of
surrogates, but that’s another problem.



TroyK wrote:
> On Oct 15, 6:48�am, wrote:
> > I would appreciate your advice about the design of the following
> > schema:
> > We have a database of products and we know that:
> > �- a product is of a certain type
> > �- a product has several parts
> > �- a product part can belong only to products of some specific type
> >
> > So we end up with:
> >
> > products {product, product_type, ...}
> > �key: {product}
> >
> > part_types {product_type, part_type, ...}
> > �key: {product_type, part_type}
> >
> > product_parts {product, part, product_type, part_type, ...}
> > �key: {product, part}
> > �foreign keys: products {product, product_type}, part_types
> > {product_type, part_type}
> >
> > The problem is the �product_type� attribute in relation �products�.
> > While redundant because it was already stated that the product is of a
> > certain type in the �products� relation, it is required as part of the
> > foreign key to �part_types�. �Still anomalies are avoided due the
> > foreign key to �products relation� (not to the primary key but to a
> > superkey).
> >
> > The obvious solution is removal of �product_type� from �product_parts
> > � and addition of a constraint to check if a product type can be
> > associated with a certain part type. However it is more difficult to
> > implement in a SQL database than a foreign key.
> >
> > The design looks awkward, it is more complicated, adds a lot of
> > redundant information, but it makes easy to enforce some constraints
> > that would need to be implemented using triggers (this is actually a
> > simplified version of the schema, in the real database there are more
> > tables that are using overlapping foreign key to �products� table).
> >
> > What do you think as being more important, sticking to normalization
> > rules or choosing a compromise that will add some redundant data but
> > under a strict control using foreign keys? Can you spot advantages/
> > disadvantages of each variant? Or perhaps you could suggest a better
> > solution�
> >
> > Thank you for your time
>
> Please excuse the self-promotion, but this looks like an example of a
> design case that I tackle in an article here:
> http://www.sqlservercentral.com/articles/Data+Modeling/61528/
>
> I am in agreement with Jon that one should favor controlled redundancy
> for the ability to implement the constraint declaratively.
>
> HTH,
> TroyK
 >> Stay informed about: design question 

wrote in message

>... I prefer to use composite foreign keys instead of
> surrogates, but that's another problem.

Composite foreign keys????

One uses composite foreign keys when the primary key being referenced is a
composite primary key. That is all.

Are you sure you don't mean "primary keys made up by composing several
foreign keys"? Just a guess on my part, since I don't really know what you
mean.






TroyK wrote:
> On Oct 15, 6:48?am, wrote:
> > I would appreciate your advice about the design of the following
> > schema:
> > We have a database of products and we know that:
> > ?- a product is of a certain type
> > ?- a product has several parts
> > ?- a product part can belong only to products of some specific type
> >
> > So we end up with:
> >
> > products {product, product_type, ...}
> > ?key: {product}
> >
> > part_types {product_type, part_type, ...}
> > ?key: {product_type, part_type}
> >
> > product_parts {product, part, product_type, part_type, ...}
> > ?key: {product, part}
> > ?foreign keys: products {product, product_type}, part_types
> > {product_type, part_type}
> >
> > The problem is the ?product_type? attribute in relation ?products?.
> > While redundant because it was already stated that the product is of a
> > certain type in the ?products? relation, it is required as part of the
> > foreign key to ?part_types?. ?Still anomalies are avoided due the
> > foreign key to ?products relation? (not to the primary key but to a
> > superkey).
> >
> > The obvious solution is removal of ?product_type? from ?product_parts
> > ? and addition of a constraint to check if a product type can be
> > associated with a certain part type. However it is more difficult to
> > implement in a SQL database than a foreign key.
> >
> > The design looks awkward, it is more complicated, adds a lot of
> > redundant information, but it makes easy to enforce some constraints
> > that would need to be implemented using triggers (this is actually a
> > simplified version of the schema, in the real database there are more
> > tables that are using overlapping foreign key to ?products? table).
> >
> > What do you think as being more important, sticking to normalization
> > rules or choosing a compromise that will add some redundant data but
> > under a strict control using foreign keys? Can you spot advantages/
> > disadvantages of each variant? Or perhaps you could suggest a better
> > solution?
> >
> > Thank you for your time
>
> Please excuse the self-promotion, but this looks like an example of a
> design case that I tackle in an article here:
> http://www.sqlservercentral.com/articles/Data+Modeling/61528/
>
> I am in agreement with Jon that one should favor controlled redundancy
> for the ability to implement the constraint declaratively.
>
> HTH,
> TroyK
 >> Stay informed about: design question 

Walter Mitty wrote:
> wrote in message
>
> >... I prefer to use composite foreign keys instead of
> > surrogates, but that's another problem.
>
> Composite foreign keys????
>
> One uses composite foreign keys when the primary key being referenced is a
> composite primary key. That is all.
>
> Are you sure you don't mean "primary keys made up by composing several
> foreign keys"? Just a guess on my part, since I don't really know what you
> mean.
>

Sorry, I was in hurry so I was not very clear. I mean some people
prefer to use surrogate (primary) keys instead of natural composite
keys and then use foreign keys to surrogates just for making joins
"faster". A bad idea in my opinion...
 >> Stay informed about: design question