SQL for Data Analytics · Designing a database
Designing a database
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.
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.
- 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.
- 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.
- Go through the documents one piece at a time. The shipment record becomes a
Shipmentstable, and details like its description and estimated delivery date become its columns. - 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.
- 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.
- 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.
- 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?
- 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.
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.
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?