Complex many to many relationships

  • Hi all,

    Here is a relatively complex design that I would like to have vetted by experts! 🙂

    Say that you need to track PhoneNumbers for different objects, such as Clients, Staff, Managers, etc. Each object could have many phone numbers associated with them.

    The basic approach to many to many would be to create join tables between objects to maintain referrential integrity (such as ClientsPhoneNumbers, StaffPhoneNumbers, etc). We have about 10 different object types for which to track phone numbers, and then the same for addresses, etc. Pretty soon we could have 20+ join tables.

    The other approach is to track parent object references on the phone, and differentiate the parents by parent type (i.e. each phone number would track the parent's ID and its type such as Client, Manager, etc.). The problem with this approach is that it does not maintain referrential integrity and you run the risk of having orphaned records in the database.

    What are other approaches to this problem?

    Thanks everyone!

    🙂

  • What might you gain by putting phone numbers into a table separate from the entity (client, agent whatever) to which they belong?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • Well, first they are not many to many then, and second it constrains the software development due to the inability for code reuse.

  • Ganga-236835 (4/25/2013)


    Well, first they are not many to many then, and second it constrains the software development due to the inability for code reuse.

    This doesn't really address the point I raised. If phone number is an attribute of [Person] or [Staff] or whatever, what would you gain by abstracting phone number into a separate table? Surely it can only cost more? Can you raise any argument which might support phone number not being an attribute of [Person]?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • I would agree that a many to many is not a the way I would choose here. There is nothing to be gained by this. Yes it is possible that you can have multiple contacts with the same phone number. For this type of thing a small amount of data redundancy is not a big deal.

    I understand what you are saying that you want to allow for as many phone numbers for a given person as they want. What I don't understand is how that creates orphans. I would establish that a foreign key with cascading delete. That way if you delete the contact, all of their phone numbers get deleted too.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • ChrisM@Work (4/25/2013)


    Ganga-236835 (4/25/2013)


    Well, first they are not many to many then, and second it constrains the software development due to the inability for code reuse.

    This doesn't really address the point I raised. If phone number is an attribute of [Person] or [Staff] or whatever, what would you gain by abstracting phone number into a separate table? Surely it can only cost more? Can you raise any argument which might support phone number not being an attribute of [Person]?

    Person can have multiple phone numbers. I've seen designs where the phone numbers are in a different table, keyed by the personid and differentiated by phone type, with the ability to add new phone types.

  • ChrisM@Work (4/25/2013)


    This doesn't really address the point I raised. If phone number is an attribute of [Person] or [Staff] or whatever, what would you gain by abstracting phone number into a separate table? Surely it can only cost more? Can you raise any argument which might support phone number not being an attribute of [Person]?

    Chris, can you help me understand your approach because the way I understand what you are describing it seems that a record for the [person] would have to exists multiple times in its table in order to track multiple [phone numbers] as the phone attributes are integrated into the person table?

    Also, I should point out that the many to many relationship I refer to is not the standard many to many model. In a one to many model, a person exists once and a phone record exists X times. In the model I am describing, the phone number exists once per object of different types (person, manager, staff, etc), but since it is not tied to a specific object type, I have called this the "complex many to many relationship" as I do not know how else to describe this model.

    G.

  • Ganga-236835 (4/25/2013)


    ChrisM@Work (4/25/2013)


    This doesn't really address the point I raised. If phone number is an attribute of [Person] or [Staff] or whatever, what would you gain by abstracting phone number into a separate table? Surely it can only cost more? Can you raise any argument which might support phone number not being an attribute of [Person]?

    Chris, can you help me understand your approach because the way I understand what you are describing it seems that a record for the [person] would have to exists multiple times in its table in order to track multiple [phone numbers] as the phone attributes are integrated into the person table?

    Also, I should point out that the many to many relationship I refer to is not the standard many to many model. In a one to many model, a person exists once and a phone record exists X times. In the model I am describing, the phone number exists once per object of different types (person, manager, staff, etc), but since it is not tied to a specific object type, I have called this the "complex many to many relationship" as I do not know how else to describe this model.

    G.

    So, you want to store a phone number only once. You could have 100 people using the number 800-555-1212 and you want to store this only once in your database, correct?

  • Ganga-236835 (4/25/2013)


    Also, I should point out that the many to many relationship I refer to is not the standard many to many model. In a one to many model, a person exists once and a phone record exists X times. In the model I am describing, the phone number exists once per object of different types (person, manager, staff, etc), but since it is not tied to a specific object type, I have called this the "complex many to many relationship" as I do not know how else to describe this model.

    G.

    Personally I think this over complicates this greatly. The number of rows sharing the same phone number is likely going to be relatively small. There will be a performance hit to normalize to this point. It also adds a little more complexity which to me seems overkill.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (4/25/2013)

    Personally I think this over complicates this greatly. The number of rows sharing the same phone number is likely going to be relatively small. There will be a performance hit to normalize to this point. It also adds a little more complexity which to me seems overkill.

    Lynn Pettis (4/25/2013)

    So, you want to store a phone number only once. You could have 100 people using the number 800-555-1212 and you want to store this only once in your database, correct?

    No, the phone number is unique only to a single object and is not shared. In that sense it is a one to many relationship as the object could own multiple phone numbers.

    G.

    Btw, thanks everyone for contributing! 🙂

  • 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

  • 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:

    First Normal Form[/url]

    Second Normal Form[/url]

    Third Normal Form[/url]

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • Although I agree in principle that most common scenario regarding phone numbers is that there is one per person, in the system that I am designing that is just not the case. The [Phone] table stores office, home, mobile, mobile2, fax numbers for the majority of parent objects, but not all as some could have no phone numbers.

    Adding these columns to each parent's object just does not make sense. There is no supporting case, at least in this system, where it would make sense to add all these extra nullable columns to each parent object's table. Abstracting it to the separate table is the most logical route.

    The challenge again then is mapping these phone numbers to variying parent objects. The solution I am leaning towards is adding the parent id and parent type columns to each phone number so that querying id "123abc" for parent "Manager" returns X amount of phone numbers for that particular business object owner. Again, the issue is that these phone numbers could remain orphaned as there is no foreign key constraint on the parent ID column as that ID could be mapped to many different business object owners.

    G.

  • is it possible, or even considered OK, to put all your different Members tables (Clients, Staff, Managers) into one table called 'Clients', and have a Foreign Key to a Table called ClientType which would detail whether they were Internal Staff, External Contacts etc.?

    then your Phone Table would only have to link to that one Clients Table.

  • Ganga-236835 (4/26/2013)


    Although I agree in principle that most common scenario regarding phone numbers is that there is one per person, in the system that I am designing that is just not the case. The [Phone] table stores office, home, mobile, mobile2, fax numbers for the majority of parent objects, but not all as some could have no phone numbers.

    Adding these columns to each parent's object just does not make sense. There is no supporting case, at least in this system, where it would make sense to add all these extra nullable columns to each parent object's table. Abstracting it to the separate table is the most logical route.

    The challenge again then is mapping these phone numbers to variying parent objects. The solution I am leaning towards is adding the parent id and parent type columns to each phone number so that querying id "123abc" for parent "Manager" returns X amount of phone numbers for that particular business object owner. Again, the issue is that these phone numbers could remain orphaned as there is no foreign key constraint on the parent ID column as that ID could be mapped to many different business object owners.

    G.

    This is an interesting case, so I'm guessing that it really depends on how important the functionality of the foreign key constraint is for your needed functionality. If it is really important, then I would lean toward having a phone table for each parent table that needs to have zero or more phone numbers linked to it. If you wanted to report on all phone numbers, you could use a union query of some sort.

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply