### 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.