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?

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.

Thanks Mark.

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

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.

yeah, and besides, the prefixes break Relational Rulez

Only if they are consistent. Random prefixes should be OK.

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

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:versus: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.

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.

+1 for natural keys.
+1 also for the phone table.

pootle flump ur codings are working excelent.

but pls don't call it a phone table, it's a client_phones table

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:I have identified the negative value of a superfluous surrogate key and index. Can you identify/particularise the true value that you allude to.

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.

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.