Page 1 of 4 123 ... LastLast
Results 1 to 15 of 58
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Using one phone table for many entities

    Hello everyone,

    Is it ok to use one table such as phone and share that table for other entitles that also use a phone? I see it as a M:M relationship; a customer, for example can have many phones and a phone can be used by many people.

    I only wanted to model it like this to cut down on the number of tables in the design. The importance of the phone numbers themselves is not important but the number of phone numbers a person has is.

    I just created a join table between the complainant table and phone and then another join table between employee and phone. I would like to know if this is an acceptable design.

    I have included a pic.

    Thanks for the help.

    ~Frank
    Attached Thumbnails Attached Thumbnails export.gif  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the design looks fine, but in my opinion, it's overkill

    i would keep the employee_phone table, as it is in a one-to-many relationship with employee

    similarly, i would keep the complainant_phone table, as it is in a one-to-many relationship with complainant

    but i would ditch the phone_type and phone tables

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    the design looks fine, but in my opinion, it's overkill

    i would keep the employee_phone table, as it is in a one-to-many relationship with employee

    similarly, i would keep the complainant_phone table, as it is in a one-to-many relationship with complainant

    but i would ditch the phone_type and phone tables

    Thanks Rudy, I was thinking the same thing but I just wanted to be sure.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Ditto.
    I like the way you are thinking, but in this case it is overkill. I would only do this if the phone itself had additional attributes that I need to track (make, model, location, service record, etc). If you all want is the number, then you are causing unnecessary overhead when someone changes their phone number, because you will need to verify whether that number has changed for all users of that phone, or whether the person is simply using a different phone that now needs to be entered into the system.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks for the input blindman. Yep, all I need is just the number and the type of phone it is such as cellular, office, etc. I think Rudy may have missed that in my post but I took his advice and seperated the common phone table. I am however sharing the phoneType table which is common to both the customer and complainant. The phoneType table will hold, cellular, office, etc. I think that would be ok like that.

    My thoughts in doing the design like that were to end up with a design that had less tables. Also, I am starting to look at things in terms of objects instead which helps me, plus I quickly began to understand that the more tables I create, the more application code I have to write.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have a surname table? if not, why not?

    then answer the related questions... do you have a phone table? if so, why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    do you have a surname table? if not, why not?
    No, no surname table. I always found it pointless to track that Jim Jones is a "Mister" and Sara Johnson is a "Ms." However, I could possibly see the value in a surname table if the database was designed to track mailing lists or that sort of thing.

    Quote Originally Posted by r937
    then answer the related questions... do you have a phone table? if so, why?
    I have the phone table because I wanted to track many different phone numbers of customers and complainants. Each type of person I would imagine could have a cellular, home, other and office phone. Without the type table, I can't see how I would add 3 or 4 different types of phones. Well... thats not true.. I could use area code and phone number as the PK but I wouldn't know what types of phones they were.

    Am I wrong?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think you missed my point, but admittedly it wasn't very clear

    it's not about the person's salutation, nor about whether the person is on a mailing list (which would require complete identification of the person, not just the surname)

    if you have a person table, do you extract the surname out into a separate table?

    CREATE TABLE surnames
    ( surname_id INTEGER NOT NULL PRIMARY KEY
    , surname VARCHAR(37) NOT NULL
    )

    INSERT INTO surnames VALUES ( 1, 'Smith' )
    INSERT INTO surnames VALUES ( 2, 'Jones' )
    INSERT INTO surnames VALUES ( 3, 'Eck Settera' )

    CREATE TABLE persons
    ( person_id INTEGER NOT NULL PRIMARY KEY
    , givenname VARCHAR(37) NOT NULL
    , surname_id INTEGER NOT NULL REFERENCES surnames ( surname_id )
    , ...
    )

    reason why you should: many people can share a surname

    reason why you should not: it would be silly overkill



    your scenario with the phones is exactly the same, except the overkill isn't so obviously silly

    i see surname as an attribute of person, not as a separate entity

    this goes back to the age-old question of data modelling: what is an entity, and what is an attribute?

    an entity is something that has identity in its own right

    certainly, it might be quite acceptable that surnames have identity in their own right... for example, in genealogy database

    but in normal business applications, you just wouldn't do it

    so, what makes your phones so special that they need to be managed independently of the people that own them?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by fjm1967
    I only wanted to model it like this to cut down on the number of tables in the design.
    I'd get rid of all the phone tables (goes from 6 tables to 2) and just have the following simple fields in employee and complainant :
    • phone_work
    • phone_mobile
    • phone_home
    • phone_fax

    You'd get pretty much the same functionality except you wouldn't be able to add new types of phone numbers on the fly but having 2 tables rather than 6 means your coding effort is going to reduce. How often are you planning on adding new types of phone?

    I also don't see how the relationship works between the employee and the complainant in your original diagram - how do you know what the complainant is complaining about (& if he has multiple complaints) - shouldn't there be a complaint table in the middle somewhere?

    Is complainant a real word?

    Mike

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    You'd get pretty much the same functionality except you wouldn't be able to add new types of phone numbers on the fly
    nor two mobile numbers

    that design is over-simplified (and, as you pointed out, problematic)

    nothing wrong with the one-to-many relationships

    employees
    employeephones
    complainants
    complainantphones
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Anyone else find Mike's suggestion hilarious - not so much the suggestion per se but the context: that Mr EAV himself proposed it

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by fjm1967
    My thoughts in doing the design like that were to end up with a design that had less tables.
    Fewer tables is not necessarily a good thing. It can actually slow down your application as it has to search through larger amounts of data in each table, or apply additional filters.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by pootle flump
    Anyone else find Mike's suggestion hilarious - not so much the suggestion per se but the context: that Mr EAV himself proposed it
    Yes, there was a certain irony in that, wasn't there?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I have a confession Rudy and I am embarrased to admit it but I was confusing surname with salutation. ahhh.. Ok, thats out of the way.
    Quote Originally Posted by r937
    if you have a person table, do you extract the surname out into a separate table?
    Absolutely not. I agree with you 100%. A person's surname is part of the identity of the table. I couldn't imagine doing that.

    Quote Originally Posted by r937
    so, what makes your phones so special that they need to be managed independently of the people that own them?
    Well, the only other reason was to guarantee data integrety. I thought I could use that phone_type table as a lookup table. I was actually thinking that maybe that would be better accomplished in the application code rather than adding a lookup table. What do you think?

  15. #15
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by mike_bike_kite
    I'd get rid of all the phone tables (goes from 6 tables to 2) and just have the following simple fields in employee and complainant :
    • phone_work
    • phone_mobile
    • phone_home
    • phone_fax

    You'd get pretty much the same functionality except you wouldn't be able to add new types of phone numbers on the fly but having 2 tables rather than 6 means your coding effort is going to reduce. How often are you planning on adding new types of phone?

    I also don't see how the relationship works between the employee and the complainant in your original diagram - how do you know what the complainant is complaining about (& if he has multiple complaints) - shouldn't there be a complaint table in the middle somewhere?

    Is complainant a real word?

    Mike
    Mike, I'm not sure that design even qualifies as 1NF.

    I have a complainant table actually below what I posted. I just cropped the image so that it wouldn't be so large. Complainant is a word that I believe exists in the industry where it is used I think.

Posting Permissions

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