Re: DBDesign Q

net__space_at_hotmail.com (Andy) wrote in message news:<edb90340.0311232036.e0eeae5_at_posting.google.com>...
> Can two entities have more than one relationship between them?
>
> A(A_ID PK,?.., bID FK)
>
> B(B_ID PK,?.., aID FK)
>
> A.bID refers to B.B_ID
> B.aID refers to A_ID
>
Yes, for example:

Employee E1 works in Department D1
Department D1 is managed by Employee D2

> 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
>
> Thank you in advance,
> Andy

A lot of people do this, seduced by how generic and flexible it is. It also saves the bother of actually working out what those pesky atrributes actually are in advance (aka "analysis"). The big downside comes when you want to query this data, and find out how complex the queries become. For example:

select p.name
from product p, attribute a1, attribute a2, attribute a3 where p.productid = a1.productid
and a1.name = 'Color'
and a1.value = 'Red'
and p.productid = a2.productid
and a2.name = '100'
and a2.value = 'Medium'
and p.productid = a3.productid
and a3.name = 'Width'
and a3.value = '7.5'

instead of:

select p.name
from product p
where p.color = 'Red'
and p.length = 100
and p.width = 7.5; Received on Mon Nov 24 2003 - 12:52:14 CET