L' Eomot Inversé (4/26/2013)
ChrisM@Work (4/25/2013)
What might you gain by putting phone numbers into a table separate from the entity (client, agent whatever) to which they belong?Maybe the entity owning the phone number has 35 phone numbers; maybe he has only one. What's the maximum number?
Good question.
Do I want to have that many columns when most entities have only 3 or fewer?Should I really have all those nasty nullable columns beibg used to create a table with in effect a number of coulmns which varies from row to row - the classical use of nullability to conceal what is really a clear violation of 1NF.
Show me a person table which doesn’t have columns firstname, middlenames, lastname. However, not every person has or is willing to reveal their middle name(s). Some are unwilling to reveal their first name(s). It’s a really good fit for most cases though, and a few nulls are more acceptable than the cost and complexity of normalising out the separate name elements.
Few persons have a unique string as one of those name elements, so there’s a valid argument for taking this one step further and having names – perhaps all names ever written down – in a lookup with a many to many link between person and names. It might take up a little less disk space (if it included only the names in use in your persons table) but wouldn’t it be a PITA to interrogate, never mind maintain!
Of course if each person has exacly 1 staff phne number, exactly one personal fixed phone number, exactly one mobile fixed phone number, and so onm - exactly one phone number in each possible category of phone numbers - then it's perfectly reasonable to put teh numbers in the persons table. Otherwise, you have to look at what the actual data is like - vast munbers of nullable col;umns are not a good idea, but a small number could be OK.
I believe this is the commonest solution, at least in my experience. Phone usage / ownership in the West and elsewhere is consistent enough that homephone, workphone and mobilephone fit most cases very well. The devices are not exactly equivalent primarily because of the attribute of usage pattern – they’re not the same as phone1, phone2 and phone3.
Anyway, the classical solution is the one mentioned by Patrick McG: a table with
person_id, phone_id, connection_type (connection type being values like "staff phone number", "personal mobile", "manager's number", "secretary's number", and so on.
I’ve seen this only once in a fair few years of db work and IIRC it was barely worth the effort (of maintaining the extra table) because there were very very few edge cases where a person wished to have more than one ‘number’ in any one category. That doesn’t mean I disagree with it – back to the first paragraph, you may be working for a business where persons routinely own or use more phone numbers than the bogstandard three. But for the vast majority of cases, three covers most bases, just like three names.
Maybe the lily could be gilded by putting the connection types in a separate table and giving them ids and having the join table refer to them by id. Some would say that that's both the underlying model for which including the type in the join table is a kludge and that this is why it's called a complex join table - because it's a three way join, not a two way join - but I prefer the "kludge" unless the types are both very very many and individually quite big; I see no problem with not bothering to have a table for an entity which has no attribute other than its identity, and consider that introducing an extra table to create an imaginary attribute (the surrogate key) would be a silly violation of Occam's principle unless it was needed to save space.
Thank you for stopping by Tom, I was hoping you would, as ssc’s authority on normalisation:
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden