Table Relationships

Last Post 28 Apr 2006 05:06 PM by cmdr_jpskywalker. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
allena
New Member
New Member

--
06 Jun 2005 12:26 PM
I have a group of 5 tables (A, B, C, D, E) with the following relationships...

There is a many-to-many relationship between Table A and Table C, with Table B being the associative table between them (i.e. Table A and Table B are both parents of Table C).

Table A is a parent of Table D.

Table C is a parent of Table E.

It seems that making Table D a parent of Table E is something I'd want to avoid. Is that a fair statement? Is there a technical term for this type of relationship? Any general comments anyone would like to share on this?

Thanks!
dbandee
New Member
New Member

--
07 Jun 2005 10:47 AM
I am not sure what u r saying. from your first paragraf B is an assoc table betw A and C
and then u r saying Table A and Table B are both parents of Table C tahts contradicts what u r saying.
if you are saying that A||--<B>--||C then how cab B be a parent for C its the opposite

And what does E and C have to do with each other?
Ado_SQL
New Member
New Member

--
07 Jun 2005 09:11 PM
The technical term for what you describe is "an implied relationship", whether or not you then want to define an explicit relationship entirely depends on what you are trying to achieve; there may be occasions when, for the sake of efficiency or to simplify a query you may want to do so.

However care must be taken to make sure the explicit relationship is the same as the implied relationship; it is far too easy to define an explicit relationship which is contrary to an implied relationship and which can destroy the integrity of the data or the design.

I am sure there are far better theorists than myself who can explain this better, but in principle that is what you have.

HTH
sqladmin
New Member
New Member

--
27 Apr 2006 08:46 AM
too bad you can't post diagrams... would make things alot easier.
cmdr_jpskywalker
New Member
New Member

--
28 Apr 2006 05:06 PM
It seems you are in a physical design where Table C is the auxilliary table of A and B, implementing the many-to-many relationship of Table A and Table B. And if Table A is parent of Table D and Table C is parent of Table E. Then making D parent of E is incorrect because of the relationship between A and C. This will also cause fan trap error when querying E through AD. Check the business relationship of D and E, you might be in a BOYCE-CODD form. If you can use the business names instead of A, B, C, etc. it will be much easier to understand .
SQLUSA
New Member
New Member

--
02 May 2006 12:45 PM
What kind of terminology is this: "Table A is parent of Table D".

What does this mean?


Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World
GoldenGal
New Member
New Member

--
23 May 2006 08:00 PM
Hey AllanA,

If you have a valid reason for associating tables D & e and enforcing that association, then do it. There's no reason not to. You don't have a cycle or a recursive relationship set, if that's what you're thinking.

If you create the relationship between tables D & E, you'll simply have two M:N relationships, C:A and C, with a 1:M relationship between A and D. Tables B & E are associative tables. So far I don't see a problem.

Just make sure that you define the relationships as they really exist. For instance, don't force a mandatory parent if that's not the requirement.

The data model is simply an expression of the business rules and regulations, the requirements, if you will. Yes, there's a methodology involved in creating the data model, but don't limit yourself to heresay or old DBA's tales. Design the model according to the requirements. If you need to enforce a relationship between tables D & E, then do it.

Have fun!
MAP
You are not authorized to post a reply.

Acceptable Use Policy