Home Forums Database Design Relational Theory A simple example which, after 15 years of relational design work, still breaks my brain - phone numbers RE: A simple example which, after 15 years of relational design work, still breaks my brain - phone numbers

  • Jeff Moden - Thursday, December 7, 2017 7:25 PM

    Thank you for the explanation.  I appreciate it.

    I don't know much about taxes for "Down Under" but in the U.S.A., taxes and the like are usually applied to the total value of the invoice rather than for each item on the invoice.  In such a case, I'd store the taxes on separate lines.  In other words, I build the invoice table in an EAV fashion where each amount listed on the invoice lives in a separate row in the table.  Any taxes or fees also live on their own rows.  So, for me, your example doesn't provide justification for listing multiple phone numbers on the same row of data. 

    That shouldn't be taken as a point of contention between you and I.  I'm just explaining the thought process that I have that justifies the use of a separate phone number table (one of the people that I work for has ~20 phone numbers).  Think of the row that identifies the individual or company as the "invoice header" table and the phone number table as the "invoice detail" table.

    I also do the same with addresses because even individuals can have more than one physical address and more than one shipping address (for example) and they can be temporally sensitive.  The same also holds true for email addresses and many other attributes of an individual, even including names.

    Yep, I tend to agree with your reasoning.

    What I usually end up with in this domain is a set of tables representing different contact protocols (phone numbers, email addresses and url's) which are subtypes of a more general contact point, and I associate contacts with sets of contact points. But that's normally a result of cardinality concerns: If a person can have more than one home phone number (for example), it becomes quite clear that at least distinct home phone numbers must be distinct rows, not distinct columns. This then renders the domain ambiguities somewhat moot. That's why I wanted to restrict the hypothetical example to one in which there is just the one home phone number, mobile, etc. It's certainly an artificial restriction in most cases, but it does sharpen the point. 

    A lot of systems I've seen do in fact model different phone numbers as different columns. Right now I'm thinking of Dynamics CRM, where a contact is composed of columns like phone1, phone2, address1_line1, address1_line2, address2_line1, etc. This, to me, is just obviously wrong. But there are certainly cases where things aren't nearly so clear!