Linking-Related Question

[oftenconfused]

I have a table called PRODUCT. For each product (or data entry) I want to be
able to select one or more uses for the product. To do this, I have the
PRODUCT table linking to a table called USE (which is on the many side of a
one-to-many relationship). Here's my question: I want to be able to
associate comments to each individual use. I thought about adding a
"comment" field to the USE table, but depending on the product, the comment
may be different for the same selection in the USE table. So, I created a
separate table called USE COMMENT. I think this is the way to go. (Please
tell me if I'm wrong.) I'm just not sure I built it correctly. Here's what
I did:

PRODUCT
ProductID
ProductName

USE
UseID
RequiredProductID
Use
UseCommentID

USE COMMENT
UseCommentID
Comment

There should be only one comment per use per product. Did I do this right
(linking through UseCommentID), or do I need to do the following (linking
through UseID):

USE
UseID
RequiredProductID
Use

USE COMMENT
UseCommentID
UseID
Comment

When do you need to link one way versus the other?

Thanks!

Andrew

 

[Ken Snell \(MVP\)]

If there will be only one comment for each UseCommentID record, tnen I would
put the comment field in the USE table and not make a separate table.

One would use a separate table when you might have more than one comment for
each UseCommentID, or when you want to keep a hstory of all comments as you
change them.

 

[oftenconfused]

Ken,

I don't think that's going to work, and here's why. You could have product
"A" and product "B" (that are both captured in the PRODUCT table), that both
link to Use "C," but who which to make different comments.

Andrew

 

[Ken Snell \(MVP\)]

So a comment is related to a PRODUCT, not to a USE, record? If so, then my
suggestion would still be valid with respect to PRODUCT and USE COMMENT
tables.

If this still doesn't seem right to you, post examples of data that you
anticipate needing to store. Where is the one-to-many relationship occurring
that you want to handle by having the separate USE COMMENT table?

 

[Ken Sheridan]

Andrew:

Firstly, the three main entity types appear to be Products, Uses and
Comments, so you start with tables of those names. BTW I'd avoid using upper
case names for tables as it will make the SQL of queries hard to read. I
also prefer plural or collective nouns as table names as I feel this better
reflects the fact that a table represents a set, but if you are more
comfortable with singular nouns I'm not at all proscriptive about this.

Not all uses can be associated with all comments and vice versa, so you now
need a table UseComments say, which models the many-to-many relationship type
between Uses and Comments and enables you to enforce this constraint when
associating a product with a use. This will have columns UseID and
CommentID, each a foreign key and together constituting the table's primary
key.

You now need to model the many-to-many relationship type between Products
and UseComments with a table ProductUses say, with columns UseID, CommentID
and ProductID. In this the foreign keys are ProductID and a composite one of
UseID and CommentID which references the composite primary key of
UseComments. To enforce the constraint that "there should be only one
comment per use per product" the ProductID and UseID columns should be made
the primary key of this table.

Diagrammatically the relationships will look like this:

Products----<ProductUses>----UseComments

Uses----<UseComments>----Comments

As you see, the ProductUses table resolves the many to-many relationship
type between Products and UseComments into two one-to-many relationship
types, and the UseComments table resolves the many to-many relationship type
between Uses and Comments into two one-to-many relationship types. By
enforcing these relationships a product can only be associated with a pair of
values of use and comment which are represented in a row in UseComments.
This latter table determines what use/comment combinations are legitimate by
having one row for each legitimate pairing.

By virtue of the two column primary key of ProductUses a product can only be
associated with one instance of each use, which in effect means there can
only be one comment associated with each use per product.

Ken Sheridan
Stafford, England