Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    normalization for phone numbers (was "Seems obvious and easy, but...")

    I am in the process of creating a custom CRM app for the company I work for right now. This question seems obvious, but the more I keep thinking about it the harder it seems to be. A contact database seems pretty simple, however normalizing it can get pretty complicated it seems.

    Would it be proper to have many to many relationships or to duplicate data? A phone number can belong to more than one person in a database, a husband and wife or thier children. Same with their address. Or a person can have a work and home address etc. Of course we all have atleast three phone numbers now, home, cell and work. If not count yourself lucky. I will be working in MySQL 5 and would like to know how I should go about normalizing for this situation. The many to many route or just having duplicate data in my database?

    You would think that there should just be some default schema's out there for routinely used database tables. It would be nice to just borrow one, but still. I really just want to do this right one time, not keep having to come back and redo. SO any help is greatly appreciated.

    Thanks,

    Fred

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I'm a pretty strict adherent to normalized databases, and even I would not throw fits over duplicated phone numbers. Also bear in mind that a single phone may server for both voice and fax.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no benefit to be attained by "normalizing" phone numbers into a separate table

    how come nobody ever asks whether it makes sense to normalize first names? it's the same thing

    thus, you would only ever record "John" once, and every John would have a FK to that row in the firstnames table

    normalized? yes, but so is the name itself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I "normalize" phone numbers into a separate table, because a person may have an indeterminate number of phone numbers. But I don't "normalize" the phone number table to make each phone number unique. Typically, I make the combination of Person/PhoneType/PhoneNumber a unique composite key.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Aug 2006
    Posts
    3
    Quote Originally Posted by blindman
    I "normalize" phone numbers into a separate table, because a person may have an indeterminate number of phone numbers. But I don't "normalize" the phone number table to make each phone number unique. Typically, I make the combination of Person/PhoneType/PhoneNumber a unique composite key.
    That is another reason I would like to have a seperate table for the phone numbers, however, I do not know if it is worth the trouble. Although now the more I think about it the more I believe I will need a seperate table for the addresses also. Since I will potentially be tieing multiple phone numbers specifically to addresses. (Long Story, has to do with reverse searching addresses for phone numbers)

    However, do you store a number or the actual type of the phone in the database?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I store both the number and the type, as you can see from my suggested composite key.

    I store locations as a separate table, normalized, because many people may work at a single location but unlike phone numbers, geographic locations are not ephemeral.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Aug 2004
    Posts
    330
    Quote Originally Posted by r937
    there is no benefit to be attained by "normalizing" phone numbers into a separate table

    how come nobody ever asks whether it makes sense to normalize first names? it's the same thing

    thus, you would only ever record "John" once, and every John would have a FK to that row in the firstnames table

    normalized? yes, but so is the name itself
    You could have a surrogate key to the Firstnames lookup table. That way if ever you had to change everyone named "John" to "Jim", you would only have to change the value of one row in the lookup table.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    But where do ya stop? Same for surnames and middle names? Birthdays?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    For constants, you could have a surrogate key to the Constants lookup table. That way if ever you had to change the value of pi, for example, you would only have to change the value of one row in the lookup table.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That sounds like a stock phrase pulled out of the Rudy Limeback Final Word in Database Principles cupboard
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wish i could lay claim to having come up with it myself, but i cannot

    don't mind repeating it, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I think you should have a table of phone number digits (0-9), each with a surrogate uniqueidentifier primary key. Then you would store the phone number as 10 separate columns of guids as foreign keys to the phone digits table.
    This makes sense, because if you ever have to change all the "6"s to, say, "4"s, you would only have to change the phone digits table rather than all the individual phone numbers. A great savings in efficiency.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    even the digits themselves aren't safe, because after all, what are they except encodings of 0s and 1s

    lotsa duplication there!! normalize those suckers too, i say

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

  14. #14
    Join Date
    Aug 2004
    Posts
    330
    Quote Originally Posted by r937
    For constants, you could have a surrogate key to the Constants lookup table. That way if ever you had to change the value of pi, for example, you would only have to change the value of one row in the lookup table.
    Except for the fact that PI is a derived value. Which would be a recursive many to many relation between measurment. The recursive duple would point to the Radius and the Circumference of the measurement table for object type of circle. The value of PI would be derived from the ratio of these values. That way, if you wanted to change the value of PI to something more simple... lets say 3 to make calcluations easy, then you would just manipulate the values of the measurements of the diameter and the circumfrence of a circle and changing the value of PI would be simple.

  15. #15
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    You could also make the phone number table recursive. This way you could have parent and child digits.

Posting Permissions

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