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

    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.

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

    Tom