DBDesign Q

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

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,
Andynet__space@.hotmail.com (Andy) wrote in message news:<edb90340.0311232036.e0eeae5@.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;|||Hi,

I think i does'nt make sence.

The reason is for the creation of each table , another table should
exists already vice-versa.

So i think u can not create even a single table as that regard.

With thanks|||It is correct on an ERD, but incorrect in a relational schema.

"Andy" <net__space@.hotmail.com> wrote in message
news:edb90340.0311232036.e0eeae5@.posting.google.co m...
> 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
> 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|||"Alan" <alan@.erols.com> wrote in message
news:bpthmj$1t348d$1@.ID-114862.news.uni-berlin.de...
> It is correct on an ERD, but incorrect in a relational schema.

That's a rather astounding statement. How do you propose to support your
assertion?

> "Andy" <net__space@.hotmail.com> wrote in message
> news:edb90340.0311232036.e0eeae5@.posting.google.co m...
> > 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
> > 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|||"Andy" <net__space@.hotmail.com> wrote in message <news:edb90340.0311232036.e0eeae5@.posting.google.co m>...

> 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
> 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

Maybe you really want an "extender table" approach, in which
the Product table holds attributes which all products have,
and additional tables to hold attributes which some, but not
all, products have, perhaps like so:

Product (ProductID PK, Name, Manufacturer, Price, Mass, Volume, ...)

Book (ProductID PK/FK, Author, Pages, Binding, ...)

Album (ProductID PK/FK, Artist, Tracks, Media, PlayingTime, ...)

Software (ProductID PK/FK, Platform, Media, ...)

--
Joe Foster <mailto:jlfoster%40znet.com> "Regged" again? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!|||raghuraman_ace@.rediffmail.com (Raghuraman) wrote in message news:<66c7bef8.0311240532.731c4819@.posting.google.com>...
> Hi,
> I think i does'nt make sence.
> The reason is for the creation of each table , another table should
> exists already vice-versa.
> So i think u can not create even a single table as that regard.
>
>
> With thanks

It is still not unusual for two tables to cross with a foreign key in
each pointing to the other. Circular relationships are not rare. As
another example,
a table of a publishers books can be linked with a table of the
publishers authors (books have authors and authors write books).

They can be murder to load (chicken or egg type question) and maintain
(deletes have the same problem), but they are not unusual.|||Whilst you can techincally create references this way (circular), I
would recommend not doing so. With a small and simple system, this
approach appears to be a good idea, however, I would suggest this is
bad practice as when you work on larger systems the complexity
increases dramatically. Don't get into the habit of doing this if you
can.

Our old back office system at work (pre me) was built with the
majority of references being circular. There were even tables which
held information on which circular link was to be used (depending on
the data). At the size of db we work with, this was a complete
nightmare. It was one of the worst designed systems I have ever seen.
Everyone on this forum would laugh if they saw the design. We have
since changed this as you would expect.

Yes, you can work with them, just explain to your colleagues why you
will be found mumbling to yourself later when it's become too complex.
If you absolutely have to do this, FFS make sure it's documented for
future developers.

Keep it simple. It will make a huge difference in the long run ! I'd
recommend spending a little longer working out an easier none circular
way of referencing the data.

That's my tuppence. You may well disagree.

"Joe \"Nuke Me Xemu\" Foster" <joe@.bftsi0.UUCP> wrote in message news:<1069701707.142710@.news-1.nethere.net>...
> "Andy" <net__space@.hotmail.com> wrote in message <news:edb90340.0311232036.e0eeae5@.posting.google.co m>...
> > 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
> > 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
> Maybe you really want an "extender table" approach, in which
> the Product table holds attributes which all products have,
> and additional tables to hold attributes which some, but not
> all, products have, perhaps like so:
> Product (ProductID PK, Name, Manufacturer, Price, Mass, Volume, ...)
> Book (ProductID PK/FK, Author, Pages, Binding, ...)
> Album (ProductID PK/FK, Artist, Tracks, Media, PlayingTime, ...)
> Software (ProductID PK/FK, Platform, Media, ...)|||In article <edb90340.0311232036.e0eeae5@.posting.google.com>,
net__space@.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@.mayo.edu
(please remove numbers to email me)