Re: DBDesign Q

In article <[EMAIL PROTECTED]>, 
[EMAIL PROTECTED] 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
[EMAIL PROTECTED]
(please remove numbers to email me)