I would like to compare and contrast two options for the design of customer contact information for a relational database.
This is the first option, a “fat” customer table.
create table t_fat_customer ( customer_id bigint not null primary key, customer_name varchar(255) not null, customer_phone_1 varchar(255) not null default '', customer_phone_2 varchar(255) not null default '', customer_tollfree_1 varchar(255) not null default '', customer_tollfree_2 varchar(255) not null default '', customer_mobile_1 varchar(255) not null default '', customer_mobile_2 varchar(255) not null default '', customer_record_created_on timestamp default current_timestamp, customer_record_updated_on timestamp default current_timestamp on update current_timestamp ); insert into t_fat_customer ( customer_id, customer_name, customer_phone_1, customer_phone_2, customer_tollfree_1, customer_tollfree_2, customer_mobile_1, customer_mobile_2 ) values ( 1, 'John Doe', '123-456-7890', '123-456-7891', '800-123-4567', '', '123-456-7892', '' ), ( 2, 'Jane Doe', '123-456-7894', '123-456-7895', '800-123-4569', '', '123-456-7896', '' );
This is the second option, a “skinny” and “properly normalized” option. It has lots of skinny tables, which some will claim are the hallmarks of “good design”.
create table t_skinny_customer ( customer_id bigint not null primary key, customer_name varchar(255) not null, customer_record_created_on timestamp default current_timestamp, customer_record_updated_on timestamp default current_timestamp on update current_timestamp ); create table t_skinny_customer_phone ( customer_phone_id bigint not null primary key, customer_id bigint not null references t_skinny_customer( customer_id ) on delete cascade, phone_number varchar(255) not null, phone_type enum( 'phone', 'tollfree', 'mobile' ) not null default 'phone', phone_type_order tinyint unsigned not null default 0, phone_record_created_on timestamp default current_timestamp, phone_record_updated_on timestamp default current_timestamp on update current_timestamp ); insert into t_skinny_customer ( customer_id, customer_name ) values ( 1, 'John Doe' ), ( 2, 'Jane Doe' ); insert into t_skinny_customer_phone ( customer_phone_id, customer_id, phone_number, phone_type, phone_type_order ) values ( 11, 1, '123-456-7890', 'phone', 1 ), ( 12, 1, '123-456-7891', 'phone', 2 ), ( 13, 1, '800-123-4567', 'tollfree', 1 ), ( 14, 1, '123-456-7892', 'mobile', 1 ), ( 21, 2, '123-456-7894', 'phone', 1 ), ( 22, 2, '123-456-7895', 'phone', 2 ), ( 23, 2, '800-123-4569', 'tollfree', 1 ), ( 24, 2, '123-456-7896', 'mobile', 1 );
Here are some stats from the design options:
| Fat option | Skinny option | |
|---|---|---|
| Lines of code: | 42 | 85 |
| Tables: | 1 | 2 |
| Columns: | 10 | 11 |
| Records: | 2 | 10 |
The first option is simpler than the second option by every measure.
I know, you want to create a more general solution. I know, they say a hallmark of good database design and “proper normalization” is lots of skinny tables. I remind you: the first option is simpler than the second option by every measure.
My advice is don’t rush toward general solutions in an effort to solve problems you don’t have.
The UI isn’t coupled to the database design in any way. You could implement any type of UI on top of either database design alternative, such as:
| Primary | Secondary | Operations | |
|---|---|---|---|
| Phone: | |||
| Tollfree: | |||
| Mobile: |
Or:
| Phone Type | Phone Number | Operations |
|---|---|---|
(My opinion is that the first UI option above is superior to the second UI option, it’s also exceedingly easy to implement with the “fat” customer table.)
Here are some implementation exercises to compare the difficulty of working with the database options:
- given a customer ID get the primary tollfree number or the primary phone number if there’s no tollfree number on record
- for each customer, fix the phone number records so that the secondary contact option is promoted to the primary contact option if there isn’t already a primary option on record, for each phone type (phone, tollfree, mobile).
- implement user interface option A above
- implement user interface option B above
- create a view to present the “fat” customer contact options as a table like in the “skinny” t_skinny_customer_phone table
- create a view to present the “skinny” customer contact options as a table like in the “fat” t_fat_customer table
- enforce a business rule which says that not more than two contact options for each phone type should be stored in the database for each customer
- check for duplicate contact phone numbers and remove them when found
- put the customer contact information into a spreadsheet and email to marketing by COB
If you are building a small/departmental application, do as you like. If you need your system to be scalable, in any sense, do no abuse it by aliasing empty strings to null. If nothing else, you’ve complicated every piece of code that has to touch it. But also, you’ve destroyed your ability to distinguish between “I don’t know this person’s phone number” and “I know this person has no phone number.”
There are, of course, other ways to model it that allow for more sophisticated representations. But a good rule of information processing systems (which is most of them) is “don’t destroy or corrupt information without good reason”.
Okay, fine. We don’t need the null values so we will leave them out. It is true that null or empty is more complicated than empty. (It also wastes a little space to use empty strings but that’s not important.) I would argue that if your application needs to know the difference between “I don’t know this person’s phone number” and “I know this person has no phone number” then null / empty string is a terrible way to model that information. Also I would posit that even if your database “required” this distinction your users wouldn’t have a clue so they would guess and you’d have bullshit information in your “properly modeled” database.
Hey Craig, I didn’t mean to bait and switch, but I took out the bit about null values in the “fat” table option because I felt it was a distraction compared to the point that I am trying to make here.
Cool. Sorry if I was the distraction! :)
lol. <3
That said, I do think treating null casually is a “data smell” in general. Not saying that’s happening here. I just see it a lot.
Honestly I totally agree, and you might not want to believe me at this point, but the reason why I put that null business in the first version of this blog post is because I feel like I should wean myself off this belief. Or maybe not. Gah!