Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2006
    Posts
    4

    Question Help in table implementation!

    Maybe you can help me folks:

    I have 3 tables (for now) which (may) have 0,N phone numbers.
    * Agent (AgentID, AgentType, etc.);
    * Facility (FacilityID, FacilityName, facilityType, etc.) and
    * Concact (ContactID, FacilityID, ContactName, etc.).

    The phone table is like this:
    * Telephone (PhoneID, PhoneCountryCode, PhoneRegionCode, PhoneNumber, InternalExtension)

    According to the normal rules, since the relationship is 0..N for these 3 tables their codes must migrate to the phone table and a check constraint should be created so as to allow only one code (AgentID, FacilityID or ContactID) to be filled at a time in the phone table.

    This sounds fair, but more entities (other than those) would demand a phone, what would make me add more columns and FKs to the phone table (and modifications in the check constraint). On the other hand, there might be entities/tables with a single phone, in which a reference (to add a PhoneID column and to create a FK) would be enough.

    A solution might be "bending" normalization and turn those 0..N relationships into N..N ones, creating the correlated tables, what I think wouldnt be so nice concerning performance, and I would have to create another table to every entity which has more than 1 phone.

    Another solution would the the creation of a single table, registering the code of the phone's owner, the phone code and a column like "PhoneOwnerType" ('A' for Agent, 'F' for facility, etc.), which both "OwnerCode", "PhoneOwnerType" and PhoneCode as PK. This is not so clean (kinda denormalization), and would demand a trigger to check if the owner's code is in the proper parent table, according to "PhoneOwnerType".

    What do you suggest in this case? What would be better concerning performance and maintenance?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I don't think it would be "bending" normalisation to make the relationship between e.g. Contacts and Phones many-to-many: in real life there may well be two contacts at the same Facility who share a phone. Performance shouldn't be a problem either.

  3. #3
    Join Date
    Dec 2006
    Posts
    4
    Quote Originally Posted by andrewst
    I don't think it would be "bending" normalisation to make the relationship between e.g. Contacts and Phones many-to-many: in real life there may well be two contacts at the same Facility who share a phone. Performance shouldn't be a problem either.
    Thanx Andrewst!

    Well, creating N:N seems to be the best idea. I just thought this "general table" would be ok, since I would have only one table, but I would have lots of work maintaining triggers, besides the awful "denormalization".

Posting Permissions

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