Checklist DB Design (EN)

The 5 Basic Rules

1. An entity is a person,thing or event. A number (for example weight) is never an entity but it is an attribute (characteristic) of an entity.
2. Every entity had excactly one PK (primary key). The primary key is unique for the entity. For example social security number for a person or licence plate number for a car.
3. Entities can have the follwing relations: 1:1, !:N, N:1 of N:M.
   • 1:1 relations are rare. If they occur, you can most probably merge the two relations into one.
   • 1:N and N:1 are the same and will ocuur in most cases.
   • N:M relation is translated into two 1:N relations via a so called connection-entity/table.
4. A 1:N relation is drawn via a line. The line has a triangle or rake on one side and just a line on the other side. The line is connected to the one-side and the triangle connects the many-side.
   
   
5. Every triangle (rake) 'belongs' to one FK. The FK is connected to the PK of the connecting entity and has the same data type (the name of the attribute, although the same in content, may differ).

Datatypes 

Data types

The most common data-type are:

Datatype	Voorbeeld
int	-2 147 648 - 2 147 649
varchar(), bijv. varchar(20)	"Big Boss 12"
date	2022-04-01
datetime	2022-04-01 18:43:12
decimal(6,2)	1250,95

 

Relation(1:N) between entities

Een relatie tussen twee entiteiten is vrijwel altijd een 1-op-meer relatie en heeft daardoor aan één kant een 'harkje'.

A relation of two entities is almost always an 1 to many relation.  The many side of the relation had the triangle or rake ('harkje' in Dutch).

('harkje')

Examples

• One person owns more (0, 1 or more) cars ad one car belongs to exactly one person.
• One school class consists out of more (most commonly more than 1) students. One students belongs to one class.
• One home work assignment can be submitted 0,1 or more times (think about Canvas). One assignment submission belongs to one student.
• One football team has more players and one player belongs to one footlball team.
• One school has more students and one student is registered to one school.

Note that all these cases are describing the most common situation. Of course, you could think of one student who is registered at two schools, but that would be a rare case. If this still would not be a very rare case and you want to design your database so that one student can be registered at 2 or more schools, you would end up with an N:M relation which is ore complex and will be described below.

Example

Suppose you have two entities, student and study coach. In order to determine the relation, ask yourself what is applicable:

one study coach	more students
one student	more study coaches

Both are possible in theory, but in the situation at our school only the first line applies.

Thismeans that the more side will be the student, hence it will get the triangle. The relation between the two relations has the tringle on the student side and a plain line on the study coach side. The student will get a FK witch will connect to the PK of the study coach.

	one side	more side
PK	has one unique PK	has one unique PK
Lijntje	No triangle just a line	Triangle ('harkje')
FK	has no FK	one FK witch points to the PK off the one side

So the ERD will look like:

Both entities have gotten a unique PK. The FK and triangle are one the same side.

Summarized: the trinagle is situated at the may side and with every trinagle comes a FK.

Or in short:

    Triangle= More= FK

N:M (veel-op-veel)

StelSuppose jeyou voorhave jetwo hebt de entiteitentities product en cleint (Dutch "klantklant").

De

The relatierealtion tussenbetween dezethese twee entitetienentities is N:M, veel-op-veel.many Eénto klantmany. kanOne immersclient meercan productenbuy kopenmore enproducts eenand one product kancan doorbe meederebought klantenby wordenmany gekocht.clients.

OmIn ditorder into eenput databasethis tekind zettenof moetrelation jeinto eenan koppeltabel(relational) aanmaken.database, Dityou ishave eento extracreate entiteit.a Datconnecting zietenitiy/table. erThe zoERD uit:will look like this:

(Note that the attribute data types are omitted in this ERD)

The entity product_klant (product-client) is the connecting entity. This entity connects the product entity and client (klant) entity in such a way that teh relation between client and product is a N:M relation.

De entiteit product_klant is de koppeltabel en deze verbind het product en de klant aan elkaar zodat er een N:M relatei ontstaat.

ViaNote dethat combiantiethe vanconnecting detable has two FK's worden(it productenalso aanhas klantentwo gekoppeld.triangles). StelThe ercombinations isof eenthese klantFK's metare hetconnecting a product to a client.

Suppose you have two clients, one with id (PK) 101 enand eenone klant metwith id 102102. enBoth stelclients je hebt eenbought product metwith the id 10 enand 11. StelTh klantecontent 101of enthe klant 102 hebben allebij product 10 en 11 gekocht. Dan staat er in de koppelconnecting tabel dewill volgendelook informatie:like this:

id (PK)	klant_id (FK)	product_id (FK)
1	101	10
2	102	11
3	101	10
4	102	11

Stappenplan maken

Creating an ERD step by step

NogAll evensteps alleto stappencreate die je moet uitvoeren om eenan ERD teare makenlisted op een rijtje:below.

1. BepaalDetermine allethe entiteiten.entities. DitThese zijnare personen,persons, dingenthings, or events about which you want to store information in the database.
2. Determine from every entity what attributes (data items) you want to store in the database. Every attribute has an unique name in the entity (f.e. you can't have two "id" or two "names" in the same entity).
3. Determine the right data types of gebeurtenissenevery waar je gegevens over wilt vastleggen.
   
   attribute.
4. BepaalCreate vanprecisely alleone entiteitenPK dein attributenevery entity. When in doubt, you can always create an (watunique) je vastlegen).
   
   id.
5. BepaalDetermine dethe datatypesrelations vanbetween allethe attributen.
   
   
entities.
6. ZorgFor ervoor dat elke entiteit een PK krijgt (bij twijfel gebruik je 'id').
   
   
7. Bepaal de relaties tussen de entiteiten. Bij eenan N:M relatierelation, hebinsert jea eenconnecting koppel tabel nodig.
   
   table.
8. TekenDraw dethe relaties.relations Hetwith harkjethe staattriangle aanon dethe meer-kant.
   
   more side.
9. BijWith elkevery harkjetriangle hoortcomes eena FK,foreign dekey. This FK verwijstpoints naarto dethe PK metof dethe entiteitentity waarmeeit deconnects relatieto bestaat.
   
   via the relation.
10. LeesThe hetPK verhaaland nogFK eenconnecting keerto dooreach enother controleerdon't necessarily need to have the same name, but they need to have the same data type.
11. Re-read the case and check if all data that needs to be stored is somehow part of jeyour alles wat er in het verhaal staat kunt vastleggen in jouw database ontwerp.ERD/

Checklist,What watoften gaatgoes er vaak fout?wrong?

1. HeeftDoes elkeall entiteitentities precieshas éénexcaclty one PK (Primar yKey)?
   
   
2. IsAre elke PK uniek, dus kan er maar één van voorkomen?
   (Achternaam kan bijvoorbeeld geen PK zijn, omdat er meer mensen zijn met dezelfde achternaam).
   Tip: meestal zijnall PK's intunique enand vaakcan hetenonly zeoccur gewoononce id.in the data set?
   For example, surname cannot be PK, because there are people with the same surname.
   Tip, you can always use (your own) id as PK.
3. HeeftDoes elkeevery attribuutattribute eenhas a datatype?
   
   
4. TelefoonnummerPhone number is geennot an int wantbecause danthe valt de eerstfirst 0 weg,will immersdisappear, 0612341234nor wordtcan 612341234
   
   you use spaces. Since you don't need to make any calculations with phone numbers, there is no need to make it an integer.
5. DatumFor any date use date as datatype.
6. If you need to record the time next to the date, use datetime.
7. Every relation, line can have only one triangle. This triangle is altijdsituated datatypeon date.
   
   the more side of the relation.
8. DatumFor plusevery tijdtriangle you need one FK. This FK connects the entity to the related entity via it's PK.
9. The PK and FK which are related have the same data type.
10. The length of an int doesn't matter. Nowadays, all int's will use the same length. You may omit the length.
11. Varchar always has a length. This length is datatypethe datetime.
    
    
maximum
12. Delength relatiethat heeftcan maximaalbe éénused 'harkje'.for Hetthis harkjeattribute. staat aan de 'meer' kant. Dus een student 'hoort' bij één klas en bij een klas 'horen' meerdere studenten. Het harkje staat in dit voorbeeld dan aan de student kant.
    
    
13. Bij elk 'harkje' hoort precies één FK. De FK verwijst naar de PK van de table waarmee deze is verbonden.
    
    
14. De PK en FK die bij elkaar horen hebben hetzelde datatype.
    
    
15. int heeft een vaste lengte, het is dus int en niet int(11).
    
    
16. varchar heeft altijd een lengte, dit is de maximaal lengte die kan voorkomen.Using varchar(5) alsfor plaatsnaamthe attribute surname is duswrong onjuist.
    
    because obviously it is too short. Varchar(300) for a phone number is wrong because it is too big and will waste database space.
17. String datatypesdata zijntypes erdo innot verschillendeexist. vormenUse inchar() Database-land.or Devarchar.() meestChar gebruiktehas zijn:a charfixed enlength varchar; char heeft een vaste lengte,and varchar heefthas eena maximalemaximum lengte.length. String
     bestaat niet in MySQL.

--