Too Many Relationships ?

[wilderfan]

I have a hypothetical question.

Let's say that a DB has 5 tables and that all the tables are connected to each other through 4 relationship lines in the E-R Diagram.

What if there are other possible relationship lines that could be established between the 5 tables.

Is it recommended that you keep the number of relationships to a figure that is 1 less than the number of tables?

 

[dsigner1]

hypothetically, simple is good.

 

[DevastatioN]

I don't believe there is any set rule, because there are always exceptions. Keeping the number of relationships to n - 1, where n is the number of tables is good for most cases, because having a number of relationships equal to the number of tables means that there is most likely a relationship loop in the database.

However, with the case of State/City/Province etc. This will create a loop that is generally acceptable.

The other case is, if a table needs to be added twice to a particular relationship. Such as a table that needs a "Starting Person" and "Ending Person" where both should be looped up from the table "tblPersons". Then the table tblPersons is added twice to the relationship window, and both connect to a different point in your master table. Even though you have only two real tables, you have two relationships and it does not technically loop.

 

[The_Doc_Man]

For your 5-table case: As long as you don't have THIS relationship layout, you are OK:

A 1:many B
B 1:many C
C 1:many D
D 1:many E
E 1:many A

As long as you don't close the relationship loop that way, you are good.