SQL for Data Analytics · Designing a database

Designing a database

Database Design Example23 min

Database Design Example23 min

A note on this video

In the video I draw the diagram in a different tool. The diagram boxes on this site work the same way: tables, columns with types, a key icon for the primary key, and lines with a crow's foot at the "many" end.

The steps in the example

In the Database Design Example video, I design a database for NavLog, a freight company that has outgrown tracking its shipments in spreadsheets and email. These are the steps the video goes through.

  1. Understand the business problem. NavLog's background, what's going wrong, what they want built, and your part in it. With a real client, you get this far by talking with them and reading their documents.
  2. Read the client's documents as data to track. A heading in their notes may become a table. An item under it may become a column, a table of its own, a column in another table, or something to drop.
  3. Go through the documents one piece at a time. The shipment record becomes a Shipments table, and details like its description and estimated delivery date become its columns.
  4. Give a detail its own table when it has details of its own. A client is more than a name, so I suggest making clients a table of their own, with the shipment holding the client's ID. A carrier's contact is a name, a phone number, and an email, so contacts get a table too, where each of those is its own column and can be required.
  5. Store a list of stages as rows. Shipments move through six stages. A stages table has an ID, a name, and a description, with one row for each stage.
  6. Look across the whole design for the same thing twice. A shipment's status turns out to be one of the stages, so it becomes a foreign key to the stages table. Its origin and destination are warehouses, so they become two foreign keys to the warehouses table.
  7. Draw it, and decide each relationship. For each line, decide which side can have many. A shipment has one carrier, and a carrier has many shipments. Then ask whether that's really true. Could a shipment use different carriers for different legs of its trip?
  8. Check the relationships with the business. Read each one through again, and confirm your assumptions with the people who run the business. In a real project that goes back and forth several times.
A client's name

In the Database Design Example video, NavLog's shipment record includes the client's name. I suggest making clients a table of their own, with the shipment holding a client ID. In the diagram I leave the client as a VARCHAR for now, as something to check with the business.

What can go wrong if every shipment stores the client's name as text, in a column of the shipments table?

How I'd answer it

If a client changes its name, every one of its shipments has to be updated. Miss one and the client looks like two clients.

A typo when a shipment is entered does the same thing. "Acme Freight" and "Acme Frieght" are two different values.

There's also nowhere to put anything else about the client, like a contact or where its headquarters are. With a clients table, the name is stored once, and the client's other details go in the same row.

Storing stages

NavLog's shipments move through six stages: booking, carrier assignment, dispatch, transit, arrival, and delivery. Each stage has a short description, like "Confirm shipping details with client." A shipment is in one stage at a time.

How should NavLog's database store the stages?