Re: design question

On Oct 15, 6:48 am, robu...@xxxxxxxxx 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.
.

Relevant Pages

  • Re: design question
    ... We have a database of products and we know that: ... implement in a SQL database than a foreign key. ... redundant information, but it makes easy to enforce some constraints ... simplified version of the schema, in the real database there are more ...
    (comp.databases.theory)
  • Re: design question
    ... We have a database of products and we know that: ... implement in a SQL database than a foreign key. ... redundant information, but it makes easy to enforce some constraints ... for the ability to implement the constraint declaratively. ...
    (comp.databases.theory)
  • Re: Diagrams
    ... How can I setup a foreign key while I'm designing a tabel??? ... > They are a simple ERD tool for visualising the schema of your database. ...
    (microsoft.public.sqlserver.server)
  • design question
    ... I would appreciate your advice about the design of the following ... We have a database of products and we know that: ... foreign key to “part_types”. ... simplified version of the schema, in the real database there are more ...
    (comp.databases.theory)
  • Re: Java is becoming the new Cobol
    ... In the Unisys 2200 environment using their Network Database Server, you get a schema work area depending on how you invoke the schema. ... However, it not only copies the schema area into the program, but the "DMCA" (Database Management Communication Area - basically, the block of information that holds your connection state) is copied in too. ... So, you're left with working-storage, which makes it visible to your program only, or common-storage, which makes it visible to programs linked in. ...
    (comp.lang.cobol)