Synopsis: Multi-column Attributes
Understand the challenges of managing multi-column attributes in SQL databases, such as storing multiple phone numbers or tags. Learn how to design tables effectively to handle multi-value attributes without compromising data quality or usability, and explore cases where separate columns are appropriate versus when to use related tables for flexibility.
We'll cover the following...
We may all have the experience of using or creating tables to store people’s contact information. Such tables often have common columns, such as the contact’s name, salutation, address, and sometimes company name.
Phone numbers are a little trickier. People use multiple numbers: a home number, a work number, a fax number, and a mobile number. In the contact information table, it’s easy to store these in four separate columns.
But what about additional numbers? The person’s assistant, second mobile phone, or field office have distinct phone numbers, and there could be other unforeseen categories. We could create more columns for the less common cases, but that really compromises the table’s outlook ...