Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    multiple phone numbers for customers

    I have a db that will require the ability to add multiple phone numbers to an account.

    Would it be faster when searching for customers, to have the clients table have multiple columns for multiple phone numbers, or to make a clientphnum table, where each number is given:

    phnID
    clientID
    phnAC
    phnLN
    phnEXT

    phnTYPE

    0 home,1 work, 2 cell, 3 fax although it may be redundant since I might want the capability of writing a description for each phone number and it can be put in there...same goes for extenstion 'phnEXT'

    description

    dialing instructions maybe a note that says 'try this one first', or specific dialing instructions if its an ivr, etc...

    ...........................
    I am afraid this method, although making it faster to find the clientID based on a phone number, along with saving space and reducing columns in the clients table may make it so retrieval will be too slow over all because of the joins that would be required.

    What do you think? any better ways?
    Last edited by arpace; 08-28-09 at 16:15.

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    You pretty much have to put the phone numbers into their own table because there is no way to predict how many phones might be connected to any given client. If somebody says 4 max and you build it that way you will find somebody with 6 the day after you go live.

    Joining to one table with an ID isn't going to slow the system down. It will even make some searches faster - such as give me all the cell phone numbers for customers.

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    Thanks Mark.

  4. #4
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    I agree with Mark. The notion that a join "costs" something or slows the database down is pure mythology, with no evidence.

    Additionally:
    you do not need a phnID, that is superfluos (and the additional index should be noted as well).
    the PK is (clientID, phnAC, phnLN)
    it could be (clientId, phnType, phnSequence)
    you can drop the "phn" as a prefix to Phone table columns (and any prefix you use in any other table), that is superfluous also:
    all the columns in Phone are Phone
    where you need to differentiate is in any ordinary join
    where you would use something like:
    Code:
    SELECT  FullName,
            Locality,
            Customer.Note,
            AreaCode,
            Line,
            Extention,
            Phone.Note
        FROM Customer,
             Phone
        WHERE Customer.ClientID = Phone.ClientID
    so the prefix or alias is always there anyway, otherwise it gets silly:
    Code:
    SELECT  cstFullName,
            cstLocality,
            cst.cstNote,
            phnAC,
            phnLN,
            phnEXT,
            phn.phnNote
        FROM Customer cst,
             Phone phn
        WHERE Customer.ClientID = Phone.ClientID
    besides, it breaks Relational rules
    do not confuse this with the rule that requires all PKs to remain fully named; they are not generic columns; theyare used across he tables. PKs have special value and are a special case. ClientID above is correct
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    The use of a surrogate key on your phone table (phnID) may be "superfluos" from a theoretical standpoint, as Derek asserts, but can have true value in the practical world. Your developers may find value in consistency of design across tables, and if so then go ahead an leave it in. I use a consistently named surrogate key on every table, because in the real world databases do not exist alone. Applications need to interact with them, and surrogate keys make excellent handles.

    But drop the "phn" prefix, and all the other prefixes on your tables and column names. They are not only annoying, but can make refactoring more time-consuming.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    But drop the "phn" prefix, and all the other prefixes on your tables and column names. They are not only annoying, but can make refactoring more time-consuming.
    yeah, and besides, the prefixes break Relational Rulez

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

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by r937
    yeah, and besides, the prefixes break Relational Rulez
    Only if they are consistent. Random prefixes should be OK.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Right. Prefix all your database object names with different GUIDs. That way there is no way to mistakenly reference the wrong column, and it is therefor less confusing.
    I think I may just design my next database that way and see if I can bluff my way past the development team...
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    The use of a surrogate key on your phone table (phnID) may be "superfluous" from a theoretical standpoint
    The use of a surrogate key is indeed superfluous from a practical standpoint as well. This is true in this instance specifically, and in any instance like this, which is the general case, but not in all instances. Superfluous, redundant, unnecessary extra appendage, call it what you will, but it is a additional physical unit that causes additional overhead, that can be eliminated.Evaluate:
    Code:
    CREATE TABLE CustomerPhone (
            CustomerID,
            AreaCode,
            Line,
            Extention,
            Note
        CONSTRAINT U_PK 
            PRIMARY KEY (CustomerID, AreaCode, Line)
        CONSTRAINT Customer_CustomerPhone
            FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)
        )
    versus:
    Code:
    CREATE TABLE CustomerPhone (
            PhoneID,
            CustomerID,
            AreaCode,
            Line,
            Extention,
            Note
        CONSTRAINT U_PK
            PRIMARY KEY (PhoneID)
        CONSTRAINT U_Phone
            UNIQUE KEY (CustomerID, AreaCode, Line)
        CONSTRAINT Customer_CustomerPhone
            FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)
        )
    You cannot avoid the U_Phone index, unless you want to allow duplicate phone nos, and all the ensuing problems. It is already a natural Relational Key.

    But you have added another index for the indolence or limited capability of the development team. Instead of one table plus one index, you have one table and two indices. If the company can handle adding extra indices for the development team, that the application or the user does not need then that is a separate problem (if they cannot handle a Relational database (natural keys, compound keys), and need single column surrogate keys fed to them, then they need education; not the database changed to suit the uneducated developers). Most companies I know have some performance criteria, and will not allow such a thing.

    In this particular instance, there are no children of CustomerPhone, so the PhoneID never gets used. 100% redundant baggage.

    There is also a problem when people have all-or-nothing, black-or-white thinking, and cannot handle guidelines and context, which leads to shades of grey. There is no need for a surrogate here, so it causes additional physical overhead when a blanket rule such as "all tables must have a surrogate key" is followed. That does not mean therefore that "no surrogate keys are allowed". No, databases are a bit more complex than that. Surrogate keys where they are necessary; where the migrated PK has become large or cumbersome, are correct. The database designer has a job to do, which requires a bit more than all-or-nothing thinking. Which means, "use surrogate keys when necessary", not never, and not always.
    Last edited by Derek Asirvadem; 09-02-09 at 00:27.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Derek Asirvadem
    The use of a surrogate key is indeed superfluous from a practical standpoint as well.
    I have a host of developers in my office writing consistent code that is reusable across all the tables in my databases who would say you have no concept of the word "practical".
    Consistency of design is certainly not impractical.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    +1 for natural keys.
    +1 also for the phone table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    +1 also for the phone table.
    but pls don't call it a phone table, it's a client_phones table

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

  13. #13
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    I have a host of developers in my office writing consistent code that is reusable across all the tables in my databases who would say you have no concept of the word "practical".
    Well, they evidently have their own private definition of "practical", since an additional index, and its attendant overhead and performance hit, does not count.

    Not the kind of developers whose opinions one should care about when building practical things like databases that everyone in the corporation (not just the developers) will be using.

    I have already answered the point re "consistency of design". WHat they are really talking about is something else, they are so inexpereiinced with Relational tables, they need all tables fed to them with a single column ID Primary Key. 100% black-or-white thinking. Any thing with a hierarchy or compound key gives them the heeby jeebies.

    The next issue is pure laziness. They have difficult with coding anything more than a single join clause (ANSI join or WHERE join). I am not saying that SQL is perfect, but that's what we've got, and we need to teach these "developers" how to use it. They need to be told that having difficulty joining on two, three, four columns is really not something to whine about, just do it. Giving them an ADDITIONAL single-column key AND INDEX, is like getting the tail to wag the dog. Here let me develop that app for you, I am a good developer ... oh, by the way, you have to double the number of indices if you want me to do it ... oh, and another thing, I can't handle compound keys ... coz they slow things down ... no, really, I am a good developer, because I told you so. Full of self-contradictions. Next thing yoou know, they will be creating completely unnecesary temp tables as intermediate steps, because they cannot navigate between the data and the projection.

    It is exactly this type of "developer" that creates "databases" that need replacement. Well designed databases (sans such "reasoning" for breaking basic rules) have no such problems. And of course, in the normal situation the Data Modeler actually has power over the model, they are not dictated to by "developers". I appreciate your situation is different, but what is missing is that separation and independence of the Modeler.

    Anyway, this is a forum on database Concepts and Design, not on how to dumb-down the database to the point where it is a bunch of separated spreadsheets, so that incapable "developers" can understand it, so we should discuss the merits of the subject and leave the third-world developer problems for a separate forum. Put another way, let's not go off on tangents re other people, you stated:
    The use of a surrogate key on your phone table (phnID) may be "superfluos" from a theoretical standpoint, as Derek asserts, but can have true value in the practical world
    I have identified the negative value of a superfluous surrogate key and index. Can you identify/particularise the true value that you allude to.
    Last edited by Derek Asirvadem; 09-02-09 at 12:46.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Derek Asirvadem
    Not the kind of developers whose opinions one should care about when building practical things like databases that everyone in the corporation (not just the developers) will be using.
    I'm sorry, but are you unclear on exactly what a developer does?
    Just to educate you a bit, the developer DEVELOPS the application. The developer does not USE the application.
    It is actually (suprise!) the USER who uses the application (developed by the developer).
    The user, in fact, rarely if ever uses the database directly.
    I hope that is not too confusing for you.
    Apparently you develop databases for yourself, when you should really keep the developers and users in mind. Understandable, since from your website it looks like you run a pretty small shop, (Sybase, reallly....), and once you learn to create Enterprise applications with better platforms you'll get a better understanding of how the database is actually just one component of the business process.
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    There are quite serious drawbacks to using natural primary keys, which I'm sure you would have run into had you designed any complex databases.
    The biggest is the way they expand into composite keys, which causes complexity with joins, wider indexes, difficulty implementing cascading changes, and numerous headaches with refactoring.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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