Results 1 to 7 of 7
  1. #1
    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:
    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 08:57.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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
    Home | Blog

  3. #3
    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?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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
    Home | Blog

  5. #5
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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
    Home | Blog

  7. #7
    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 Attached Thumbnails dbdesign.png  
    Last edited by Prodoc; 07-30-07 at 12:19.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •