Database (SQL) design tips

[wtd]

This first entry is inspired by some wackiness recently encountered by the author. The following targets MySQL, which isn't exactly SQL standards compliant.

Let's say we want a database which keeps track of various bits of information on a particular person. What kind of info?

• Name
  
• Age
  
• Country
  
• Phone number

So, let's look at how we'd organize this. This is a first try.

code:

CREATE TABLE people (    name VARCHAR(100) NOT NULL,    age INT NOT NULL,    country VARCHAR(50) NOT NULL,    phone_number VARCHAR(14) NOT NULL );

Now, there are a lot of things wrong with this, but let's start with the most basic. Every table should have some column that's guaranteed to be unique. A name is not necessarily guaranteed to be unique.

Instead we generally use an auto-incrementing integer. we also tell the database that column will be the primary key for the table.

code:

CREATE TABLE people (    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100) NOT NULL,    age INT NOT NULL,    country VARCHAR(50) NOT NULL,    phone_number VARCHAR(14) NOT NULL );

Now, onto the next mistake. We have only a single field for the name. What if we just want the last or first name? Well, we have to do some expensive processing to get them apart, and there's no guarantee there will be more than one name.

Let's just have two columns instead.

code:

CREATE TABLE people (    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,    first_name VARCHAR(50) NOT NULL,    last_name VARCHAR(50) NOT NULL,    age INT NOT NULL,    country VARCHAR(50) NOT NULL,    phone_number VARCHAR(14) NOT NULL );

Now, what else are we doing wrong?

The phone number is a string with space for 14 character, which just accomodates something like:

code:

(555) 555-5555

But again, what if I want to rearrange that or get at parts of it independently? How about I store the components separately...

code:

CREATE TABLE people (    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,    first_name VARCHAR(50) NOT NULL,    last_name VARCHAR(50) NOT NULL,    age INT NOT NULL,    country VARCHAR(50) NOT NULL,    phone_area_code VARCHAR(3) NOT NULL,    phone_excange VARCHAR(3) NOT NULL,    phone_number VARCHAR(4) NOT NULL );

Oh, and I also want the option to not have any data for the phone number.

code:

CREATE TABLE people (    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,    first_name VARCHAR(50) NOT NULL,    last_name VARCHAR(50) NOT NULL,    age INT NOT NULL,    country VARCHAR(50) NOT NULL,    phone_area_code VARCHAR(3),    phone_excange VARCHAR(3),    phone_number VARCHAR(4) );

Now, that's a lot of data, and we have a problem. What if home_phone_exchange has a value, but home_phone_number is null?

How about we separate those into another table?

code:

CREATE TABLE people (    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,    first_name VARCHAR(50) NOT NULL,    last_name VARCHAR(50) NOT NULL,    age INT NOT NULL,    country VARCHAR(50) NOT NULL,    phone INT REFERENCES phone_numbers(id) ); CREATE TABLE phone_numbers (    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,    area_code VARCHAR(3) NOT NULL,    exchange VARCHAR(3) NOT NULL,    number VARCHAR(4) NOT NULL );

Now we can easily have phone numbers as optional pieces of data, but without allowing incomplete information. We also avoid making our people table overly complicated.

Of course, two get back to something that looks like our ideal initial table, we might use a select statement like:

code:

SELECT    people.id,    CONCAT(people.first_name, ' ', people.last_name) AS full_name,    people.age,    people.country,    CONCAT('(', phone_numbers.area_code, ') ',           phone_numbers.exchange, '-',           phone_numbers.number)       AS phone_number FROM    people INNER JOIN phone_numbers ON people.phone = phone_numbers.id