A pattern I’ve seen several times is:
- Customer table – CUSTOMER ID,
- Contact table – CUSTOMER_ID, CONTACT_TYPE, CONTACT_NUMBER, OTHER
The use case for the tables is:
SELECT
Home_Num.CONTACT_NUMBER as PHONE_NUMBER,
Mobile.CONTACT_NUMBER as MOBILE_NUMBER,
Email.OTHER as EMAIL,
<other fields>
FROM Customer
LEFT JOIN Contact as Home_Num
ON Customer.CUSTOMER_ID = Home_Num.CUSTOMER_ID
and Home_Num.CONTACT_TYPE = 'Home'
LEFT JOIN Contact as Mobile
ON Customer.CUSTOMER_ID = Mobile.CUSTOMER_ID
and Mobile.CONTACT_TYPE = 'Mobile'
LEFT JOIN Contact as Email
ON Customer.CUSTOMER_ID = Email.CUSTOMER_ID
and Email.CONTACT_TYPE = 'Email'
Usually there is an assumption throughout the rest of the application that “each customer only has one home number” which I assume is made to simplify the UI design. In principle there could be infinitely many contacts attached to each customer but this pattern still turns up in places where that isn’t a possibility.
Is there a reason people design things this way? I’ve seen too many instances like this to assume it is accidental.
1
Considering there are PHONE_NUMBER and MOBILE_NUMBER in the query, probably the PHONE_NUMBER refers to the land line that is common to have a one-to-one relation with the customer, it wouldn’t be unusual neither a one-to-many relation between PHONE_NUMBER and Customer. It isn’t a UX issue, it is a real model.
user453247 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Is there a reason people design things this way?
Because, “What is your personal UUID?” doesn’t produce useful answers as frequently as, “What phone number do we have for you?”
If it did tracking people would be so much easier. But in this world people can’t agree on a single system to ID people with and people refuse to memorize an ID for every darn system they interact with.
Which is why you have to bribe people with discounts to get them to carry around loyalty cards with the ID you issued to them.
Without a scheme like that we need something they already have memorized. Most people have and can remember their phone number. But yes, some people have more than one. Some numbers are associated with more than one person.
So no, this is not at all a perfect ID. This is just something to search for in an attempt to pull up their record. Without it you have to hope you’re not the first person to bother to ask them how to spell their name. *
* Transliteration is not perfect either.
which I assume is made to simplify the UI design
The UI is just fields that you type in. The UI doesn’t care. But this is a User Experience issue. The fewer questions it takes to get to the right record the better.
I typically see this pattern for composite objects for example a customer can have separate billing and shipping addresses (each with multiple fields).
Assuming each customer has exactly one billing and one shipping address this could be represented as a single table with prefixed field names billing_... and shipping_... or it could be represented using two tables.
Generally more tables offer more flexibility for future enhancements/changes and less tables result in simpler queries (I am certain there are exceptions to both of these points).
With a reasonable amount of effort, either option can be refactored to the other one, hence it becomes a matter of opinion about which solution is “better” or “more flexible” and there will be a question about if you are going to need the extra flexibility.