Chapter 2: Data Modeling

A single source of truth

When designing a database, it is important to have a single source of truth. This means that we should avoid duplicate data.

For example, if you have a table of users, you should not have a table of users and a table of admins. Instead, we should have a table of users and a column in that table that specifies whether or not the user is an admin.

Avoid duplicate data
Avoid data that can be derived from other data.

That second point is the answer to "How do I know when to stop putting down attributes and columns." It is a hard scope boundary when modelling relational databases. What does it mean though?

Consider the Warehouse example from one of our first workshops. It was the first time we tried modelling databases, and the instructions we were given were purposefully vague. It was something like "Brainstorm all the aspects of a business you would expect to have a database, and try and create their ER diagrams." So I had something like this:

id	name	position	salary	location
1	Eric	Sales	20	Wellington

Then, naturally when thinking about businesses you would think Profit and Loss, Revenue and Expenses...

receipt	sales	revenue	item_barcode
482*****	2	200	00299****

Or like:

department_id	discount_rate	selling_price	item_name	item_barcode
Garden	0.2	20	Pot Black	9983***

Which is TOTALLY WRONG BTW DON'T DO THAT

Why is it wrong and cringe? "Avoid data that can be derived from other data"

Look at revenue for example. It's calculated from other facts — how many sales, how many items, discounted or not, which department — all added up. (Careful: revenue is just the income side. Subtract expenses and you get profit, which is a different derived number again.) Natural way of thinking for most business graduates and students. This is NOT the right way to think in this paper. We are going to strip all this unnecessary information, and keep a RECORD OF FACTS.

Now that's not saying that facts cannot change — they most likely will — but the design principle is: the database stores the raw facts, and anything derivable gets calculated at query time (that's literally what SQL's SELECT/SUM are for). Because revenue is recomputed fresh from the facts every time you ask, it can never go stale or disagree with the data underneath it.

Entities and Attributes

Entities are the kinds of things you keep records about. A good test: "Would I keep a file, card, or register of these?" If yes, it is probably an entity.

Attributes are the facts you record about each entity. Each attribute gets a type (varchar, integer, date) — same idea as a paper form having a date box, a tick-box, a free-text field and so on. The form shape is fixed, but the values differ per record.

NOTE: One confusion that keeps coming up: an entity type is the overarching kind of thing, e.g. "Patient" in a hospital. An entity instance is one specific patient, e.g. "Jane Doe, DOB 2000-01-01". Cardinalities (below) are pretty much all about instances.

Cardinality

The Cardinality of a relationship is how many unique instances can sit on each side of it.

1:1 — each store has one manager on duty, and that manager is in charge of one store.
1:N — one prescriber can write many prescriptions, but each prescription is written by only one prescriber.
N:N — a prescription can contain many medicines, and a medicine can appear on many prescriptions.

Cardinality matters because it is a constraint, a hard rule the design enforces.

Notation

There are two main conventions for writing cardinality on a diagram:

Chen-style labels — just write 1:1, 1:N, M:N on the line. Easy.
UML-style — write low..high at each end, where * means many.

The UML version has one extra trick: it distinguishes optional (0) from mandatory (1) participation. Chen's 1:N does not show whether a patient must have at least one prescription on file or may have none. UML can say 0.. meaning "possibly none" and 1.. meaning "at least one required".

Keys

Every entity needs some attribute(s) guaranteed unique per instance/entry so records do not get confused and mixed up.

Key	What it is	Example
Super Key	Any set of attributes that uniquely identifies an instance. Technically, if you take every single column of a table and put them in a list, that list is a super key (one of many). It ticks the box of being able to "uniquely identify" an entry - assuming your database is correctly modelled as a relational database (one of the requirements was that every row must be unique).	Patient name + Date + Doctor + Medicine + Pharmacy
Candidate Key	A minimal super key. Take a super key and strip it down to just what is needed to be unique. These will be potential primary keys	The barcode of an item
Primary Key	The candidate key that you choose and mark "PK" on the diagram	The barcode of an item - ONCE you have declared it to be
Foreign Key	A borrowed Primary Key from another table, used to link the tables together	Customer loyalty number if it's a primary key in another table

Entity Relationship Diagram

An easy way to visualise all of the above is through an entity relationship diagram (ERD). An ERD shows the entities, their attributes and keys, and the relationships between them with their cardinalities. When the model gets implemented, each entity typically becomes a table.

Diagrams

Drawing diagrams using tools such as https://draw.io can be a good way of visualising your database design. But, as your database grows more complex, we want to look at using a full ERD software. These tools allow us to create a database design and then generate the SQL code to create the database. They also have the ability to do analysis on your database design to find potential problems.

A good example is MySQL Workbench or ERD-Editor. Or - for the purposes of 512: https://learn.datascie.nz/.

Turning Diagrams into Tables

Once the ER diagram has been done, converting it to a relational database should be quite logical. Three rules will cover almost everything.

Rule 1: Every entity becomes a table

The columns of the table are the attributes of the entity. Each instance of the entity becomes a row. The PRIMARY KEY is marked.

For example (context: Hospital patient record): Patient becomes a Patient table with NHI as the Primary Key. The NHI is an attribute, but so is the dob and the sex or address.

Rule 2: A 1:N Relationship becomes a Foreign Key column on the "N" side.

Let's have an example for this one. Patient gets discharged from the hospital and the doctor writes a prescription for the patient to take to the pharmacy.

Two entities - Patient : Prescription

"I am a patient, how many prescriptions can I have?" - More than 1.

"I am a prescription, how many patients am I allowed to have/serve?" - I've never seen a prescription that has two patients' names on it - so ONLY one. A prescription is not legal if it is not named either - so it MUST be ONE and ONLY ONE.

So, the cardinality of Patient:Prescription is 1:N - read from left to right = One patient can have multiple prescriptions.

This means: Every row in the Prescription table (remember, the "N" side) gets a PatientNHI column holding the PRIMARY KEY of the Patient table - which acts as the FOREIGN KEY when describing it in the context of the Prescription table.

(Side note for completeness: a 1:1 relationship also becomes a foreign key column — you can put it on either side, and usually pick the side where the relationship is optional. So the three rules are really: entity → table, 1:1 or 1:N → FK column, N:N → its own table.)

Rule 3: An N:N Relationship becomes its own table

Let's have another example.

Suppose I am Netflix and I'm setting up a database that lists all the movies we have.

id	title	year	genre
1	Star Wars: Episode 1 - The Phantom Menace	1999	Sci-Fi
2	Halloween	1978	Horror
3	A Nightmare on Elm Street	1984	Slasher
..	..	..	..

But - when making the movie filter part of the website, a movie (e.g. Halloween) can have multiple genre (e.g. 'Horror' and 'Slasher'), and each genre (e.g. Horror) can have multiple movie(s) that belong to them (e.g. 'Nightmare on Elm Street', 'Frankenstein').

movie_id	title	year	genre
1	Star Wars: Episode 1 - The Phantom Menace	1999	Sci-Fi, Adventure
2	Halloween	1978	Horror, Slasher
3	A Nightmare on Elm Street	1984	Slasher, Horror
..	..	..	..

The above example is not going to work. Horror, Slasher and Slasher, Horror are TWO completely different values to a computer, but they mean the same thing - both Halloween and Nightmare on Elm Street are Slasher/Horror films. So when you ask a computer to "Fetch me every single film that is a Slasher/Horror film", it can only do dumb exact matching on that cell - it has no idea the two orderings mean the same thing, so it will miss films.

As per the definition: This is a N:N cardinality.

Even if you spell it out down to the exact minute detail and fetch "Horror, Slasher", that will only get you "Halloween" - Nightmare on Elm Street stays invisible because its cell says "Slasher, Horror".

What is the solution?

Answer

It "...becomes its own table."

Step 1) Drop the 'Genre' column in movie

movie_id	title	year
1	Star Wars: Episode 1 - The Phantom Menace	1999
2	Halloween	1978
3	A Nightmare on Elm Street	1984
..	..	..

Reason: We are creating a new table for genre and remember we don't want duplicates (ctrl + c, ctrl + v) of the data living in different tables - because if we update one of them, there will be a chance we forget to update the other one. It defeats the purpose of a "single source of truth".

In the table below, genre_name will be the new genre.

Primary Keys are...

"genre_id" (for the genre table) and "movie_id" (for the movie table)

genre_id	genre_name
1	Action
2	Horror
3	Adventure
4	Sci-Fi
5	Slasher

Now, we are able to properly assign multiple genres to multiple movies (N:N) via an associative entity (also known as junction table).

Associative entities map two or more tables together by referencing the primary keys of each data table - which in effect means it will contain a number of foreign keys (see table above for definition).

In this case, we are going to create an associative entity called movie_genre.

genre_index	movie_id	genre_id
0	1	3
1	1	4
2	2	2
3	2	5
4	3	2
5	3	5

Notice how this way, we are able to assign movie_id = 2 -- Halloween to MULTIPLE different genres (genre_index entries 2 and 3) via the genre_id foreign key in the movie_genre associative entity.

movie_id is the primary key of the movie table - but acts as the foreign key in the associative entity.

It was born in movie and so it has a movie "citizenship visa", but when it visits a foreign table movie_genre it holds a "visitor's visa". Same logic applies to genre_id.

QUIZ: Does the Primary Key exist for the movie_genre junction table? If so, what is it?

Trick question - there are TWO defensible answers

Option A (the textbook answer): the primary key is the combination (movie_id, genre_id) - a composite primary key. The fact "Halloween is a Slasher" should only ever be recorded once, and making the pair itself the PK means the database physically refuses a duplicate row like a second (2, 5).

Option B (what this table does): add a surrogate counter like genre_index. Perfectly legal and common in the real world - if you ask the computer "Fetch me entry number 4", it will fetch you ONE row and ONE row only - in this case the pair ('3','2') - which are the foreign keys we use to JOIN back to their respective tables to present readable data (more on JOINs later).

BUT beware: genre_index being unique does NOT stop someone inserting a row (6, 2, 5) - a duplicate "Halloween is a Slasher" fact. If you go the surrogate route, you should also tell the database that the (movie_id, genre_id) pair must be unique.