Re: DBDesign Q

From: Paul Tiseo <tiseo128.paul23_at_mayo.edu>
Date: Tue, 25 Nov 2003 15:41:16 GMT
Message-ID: <MPG.1a2d42255e44798f9896b6_at_news.easynews.com>

In article <edb90340.0311232036.e0eeae5_at_posting.google.com>, net__space_at_hotmail.com says...
> Can two entities have more than one relationship between them?

There can be many individual relationships between tables. However, the question is, does your design require it?

> 2. Does it make sense?
>
> Product (ProductID PK, Name,?, SpecialAttrID FK)
>
> Attribute (AttrID PK, Name,?.,ProductID FK)
>
> Product can have many attributes. One attribute belongs to only one product.
> There is only one main("special") attribute for each product

I would think that what you might want could be satisfied with a simple one-to-many:

CREATE TABLE Product (

    ProductID int NOT NULL,     ProductName varchar(18) NULL,
    CONSTRAINT PK1 PRIMARY KEY CLUSTERED (ProductID) )

CREATE TABLE Attribute (

    AtrributeID      int            NOT NULL,
    ProductID        int            NOT NULL,
    AttributeName    varchar(18)    NULL,
    MainAttribute    bit            NULL,

    CONSTRAINT PK2 PRIMARY KEY CLUSTERED (AtrributeID),     FOREIGN KEY (ProductID)

       REFERENCES Product(ProductID)
)

Other appropriate constraints (depending on your target platform) would limit one MainAttribute being "true" within a set of attributes for one given product.

Alternatively, does one attribute (guessing something like "height") really only occur for one product or many? You might have a many-to-many relationship here. It might be that you need three tables, with one being an associative table between Product and Attribute:

CREATE TABLE Attribute(

    AtrributeID int NOT NULL,     AttributeName varchar(18) NULL,     CONSTRAINT PK2 PRIMARY KEY CLUSTERED (AtrributeID) )

CREATE TABLE Product(

    ProductID int NOT NULL,     ProductName varchar(18) NULL,
    CONSTRAINT PK1 PRIMARY KEY CLUSTERED (ProductID) )

CREATE TABLE ProductAttribute(

    ProductID        int    NOT NULL,
    AtrributeID      int    NOT NULL,

    MainAttribute bit NULL,
    CONSTRAINT PKProductAttribute

       PRIMARY KEY NONCLUSTERED (ProductID, AtrributeID),     FOREIGN KEY (ProductID)

       REFERENCES Product(ProductID),
    FOREIGN KEY (AtrributeID)

       REFERENCES Attribute(AtrributeID) )

These are some alternatives for you to consider.

---

Paul Tiseo, Systems Programmer
Research Computing Facility, Mayo Clinic tiseo128.paul23_at_mayo.edu
(please remove numbers to email me) Received on Tue Nov 25 2003 - 16:41:16 CET