A Test To See If You Write Sloppy Software Page 5

Relational Database

Imagine writing an entire application and having only one database table! It’s done all the time.
If you have more than 10-15 columns in any table, it’s almost certainly a bad design. If you ever
find yourself with a table that looks like this:

Drivers
----------
Driverid int,
Lastname text,
Firstname text,
Job1_name text,
Job1_address text,
Job1_date text,
Job2_name text,
Job2_address text,
Job2_date text,
Job3 
...
States_available text

You get the idea. If you find yourself repeating columns like that, it means you should split into
two tables (or more). If you are using a “text” field to store date entries, it’s also a bad sign.
Give yourself 1 point if you store dates and other data types in proper fields.

This table should have been split into two like this:

Drivers
--------------
Driverid int (autonumber),
Lastname text,
Firstname text,

Driver_jobs
--------------
driver_jobid int (autonumber),
driverid int, (relates to drivers table)
Name text,
Address text,
Start_date date

Another good sign of bad design is using explode()/implode() on your data before accessing the
database. If you are storing a bunch of values in one big text field, say a big list of states,
that’s a horrible design. In the original “drivers” table above, the states this driver is available
in is stored as a comma-separated list in one field. The proper solution is to create a third table
like this:

Driver_states
----------------
driverid int, (relates to drivers table)
state_id char(2) (standard 2-digit state code)

Now that single awful table is broken into three properly-normalized database tables. This may
sound like a pain to do, but later if you expand your application, you will appreciate having
properly-formatted data.

Give yourself 1 point if you have properly-normalized database tables.