If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Relationships and the consequences

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 07-30-07, 07:53
Prodoc Prodoc is offline
Registered User
 
Join Date: Mar 2004
Posts: 13
Relationships and the consequences

In the ERD notation there are identifying and non-identifying relationships. Basically all info I could find on the two relationships only provide a very short explanation about the differences between them. It comes down to the following quote from an earlier forum post:
Quote:
Identifying
- primary key attributes of the parent entity become part of the primary key of the child entity

Non-Identifying
- primary key attributes of the parent entity become part of the non-key attributes of the child entity
Since I'm new to (proper) db design, descriptions in these lines don't provide enough information for me.
I understand the consequence of using the second, non-identifying, relationship. You will get a normal additional column in the child entity. What does it actually mean when an attribute becomes 'part of the primary key' when we are dealing with the first, identifying, relationship?

How should someone decide to use which relationship?

As an example, I'm currently working on a db design in the program Power*Architect. Here I've got multiple entities (tables including 'Venues' and 'Persons') which link to a 'Contacts' entity which contains columns for address, phone numbers, e-mail address, website, etc. The reason I choose to create a separate 'Contact' table is because there are going to be about 4 entities which need this info. Instead of adding the same contact columns to all 4 of them, I figured it would be more appropriate to split the lot. A contact entry can not be linked to more then one entry from one of the other 4 tables.
I drew an identifying relationship from all 4 tables to the 'Contacts' table (the horizontal line should be solid):

[Persons]--|--------0|--[Contacts]
[Venues]--|--------0|--[Contacts]
etc.

As a consequence, each ID of the 4 tables became part of the primary key in the child 'Contacts' table. How will this 'part of the primary key' aspect affect the situation? Doesn't a situation like this require 4 IDs from each of the 4 tables for a contacts entry to be added? Because of this assumption, it almost seems to me that an identifying relationship should only be used to solve a many-to-many relationship. Am I correct about this?
Did I make the right move with creating a separate 'Contacts' table and using it with an identifying relationship? A non-identifying relationship didn't seem right since a contacts entry can't exist on it's own.

Last edited by Prodoc; 07-30-07 at 07:57.
Reply With Quote
  #2 (permalink)  
Old 07-30-07, 08:06
gvee gvee is offline
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,156
Quote:
Originally Posted by Prodoc
A contact entry can not be linked to more then one entry from one of the other 4 tables.
A 1:1 relationship should be stored in one table...
__________________
George
Twitter | Blog
Reply With Quote
  #3 (permalink)  
Old 07-30-07, 08:36
Prodoc Prodoc is offline
Registered User
 
Join Date: Mar 2004
Posts: 13
Quote:
Originally Posted by georgev
A 1:1 relationship should be stored in one table...
Always, despite the fact that this will mean that I would have to add the 12 columns from the Contacts table to all four other tables?

Would you mind to shed some light on the other questions as well?
Reply With Quote
  #4 (permalink)  
Old 07-30-07, 08:38
gvee gvee is offline
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,156
Quote:
Originally Posted by Prodoc
this will mean that I would have to add the 12 columns from the Contacts table to all four other tables?
Howdyafigure?
__________________
George
Twitter | Blog
Reply With Quote
  #5 (permalink)  
Old 07-30-07, 08:50
Prodoc Prodoc is offline
Registered User
 
Join Date: Mar 2004
Posts: 13
Quote:
Originally Posted by georgev
Howdyafigure?
In the provided example a Person and a Venue will both have contact details (address, phone numbers, e-mail address, website, etc.). If you say a 1:1 relationship should be stored in one table, I would have to merge the Contacts table into Persons as well as into Venues, no? This would mean that the 12 columns of the Contacts would go in the Persons and Venues. This will have to happen for 2 other tables as well. The result would be 48 columns in 4 separate tables while it could have been 12 columns in just one Contacts table. If I decide to change the contacts columns, I would have to do this in four places.
Reply With Quote
  #6 (permalink)  
Old 07-30-07, 10:26
gvee gvee is offline
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,156
If People and Venues share the same attributes then I see no problems.
E.g. Imagine a HR system with employees, leavers and new-starters; they all have the same attributes (name, d.o.b., address, etc) so do we stick them in 3 separate tables?

I think it would be useful if you post your current design for your tables. I think we might be getting our wires a little crossed
TableName(PrimaryKey, ForeignKey, Field1, Field2, ... , FieldN)
__________________
George
Twitter | Blog
Reply With Quote
  #7 (permalink)  
Old 07-30-07, 11:14
Prodoc Prodoc is offline
Registered User
 
Join Date: Mar 2004
Posts: 13
Quote:
Originally Posted by georgev
I think it would be useful if you post your current design for your tables. I think we might be getting our wires a little crossed
Indeed :-)

Unfortunately my 'client' does not allow me to publish the complete concept but here's an excerpt of the db design which should be enough to illustrate the situation.

Users and Persons are split because there are situations where we want to specify a person which isn't a users within the system yet. I'm might merge the two tables later on, I'm not sure yet.
In table Venues, venue_contactman is a FK containing the person_id PK from the Persons table.
In table Contacts, contact_country is a FK containing the country_id PK from the Countries table.

As you can see, the Contacts table merely contains the contact details itself, it's not an object in the real world as a person or a venue.

Basically all my questions from the first thread post remain the same.
Attached Thumbnails
Relationships and the consequences-dbdesign.png  

Last edited by Prodoc; 07-30-07 at 11:19.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On