Database design and normalization

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

New website: Take Two

Hey Craig. I’ve made some changes (hopefully improvements!) to the website:

Only the home page is working at the moment. I will get the other pages working soon, hopefully today.

Please do let me know (in no uncertain terms!) what you think of the new design. I’ve gone “mobile first”, so it should look respectable on your smart phone.

A note for web designers

Every now and again I have this problem: I see an ad for a product I’m interested in on a web page just at the same time that I click on a button or link to take me to the next page. I click the back button to go back and see the ad, but the ads are regenerated and the ad that I saw isn’t there any more. Designers who are serious about maximising sales should avoid this happening to their users. When you click back you should see what you saw last time.

How To Design A Good API and Why it Matters

Today I watched: How To Design A Good API and Why it Matters. Good talk. Favourite quote:

Inheritance violates encapsulation

Oh, and I followed on with: A Brief, Opinionated History of the API wherein (t=28:14) Bloch says it’s an API if you can answer yes to both of these:

  1. Does it provide a set of operations defined by their inputs and outputs?
  2. Does it admit reimplementation without compromising its users?