design question

#41

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:

Quote:

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.

#42

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:

Quote:

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.

#43

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:

Quote:

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.

#44

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:

Quote:

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.

#45

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:

Quote:

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.

#46

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:

Quote:

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.

#47

robur.6 (AT) gmail (DOT) com wrote:

Quote:

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

#48

robur.6 (AT) gmail (DOT) com wrote:

Quote:

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

#49

robur.6 (AT) gmail (DOT) com wrote:

Quote:

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

#50

robur.6 (AT) gmail (DOT) com wrote:

Quote:

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