A Simple Database Design with MySQL Workbench Part 1.
ER Diagram, type of relationships, and type of keys.
This is a two parts serie on how to design a simple database to manage post school activities for children. The database will have to contain details for every child, every person responsible for the child, the type and price of each activity and which days each children will be able to attend.
When designing a database is important to decide in advance the number of tables we will need to create and the relations between this entities. (From now on we will refer at “tables” and “entities” as synonyms).
Entity Relations Diagram
Here we created one using MySQL Workbench “Reverse engineer” tool:
We have 4 main tables:
- “carer” table contains details of each Adult responsible for a child.
- “child” table contains details for each child.
- “activity” table contains details for each activity.
- “weekday” table containing days names and IDs.
There are a few constrains to take in consideration:
- Each child can be associated with more than one responsible person, but a child cannot enroll in the activities without a carer.
- Each adult can be carer for one or more children.
- A child is assumed to partecipate in activities on a pattern attendance such that she attend every week on the same days.
- A child can participate to more than one activity and any activity can be attended by more than one child.
In the ER model we represent entities and the reletionship between them. In the diagram entities are represented by the rectangles and the relationship by the lines connecting them.
There are 3 types of relationtiships:
- One-to-one: is rarely used since we could just rapresent this kind of relationship in a single table.
- One-to-many: is used when entries of one table can be associated with more than one entry of another table (i.e. one child can only be associated with one adult but any adult can be associated with more than one child).
- Many-to-many: is used when each entry of two associated tables can be related to one or more entries of the other. (i.e. each child can attend one or more activity and one activity can accomodate one or more children).
Here we see how this relationship are represented in the diagram. The single line with two horizontal dashes reperesent a single relation, the one on the bottom resambling a crow foot represent many relations. Altogether this line represent a one-to-many reletionship.
Many-to-many relationships need an extra table to be represented in our design. This is accomplished by creating “link tables”. As the name says this tables work like a “bridge” between two or more tables. We created 3 entities for this purpose:
- “carerchild” connects “child” and “carer” tables.
- “childactivity” connects “child” and “activity” tables.
- “daychild” connects “child” and “weekday” tables.
Primary Keys and Foreign Keys
- Primary keys (PK) uniquely identify each row of the table. The primary keys guarantee unique data and also provide fast access to data if the primary key is used in the query. As shown in the illustration the child_id column is a primary keys and is set to be auto-incremental(AI).
- Given two tables A (parent) and B (child) a Foreing key links each record from child table with the corresponding primary key in the parent table. The parent table does not contains a foreign key, only the child table does. In the next picture the child_id column in the left hand side table is both PK and FK to child_id column in the right hand side table.
In the left hand side table both columns are set as PK and each of them contains a FK to the right hand side tables we want to relate with a many-to-may relation.
It’s all for Part 1, in the next chapter i will present the full code to build the each entity and a description of the data type used.
Thanks for reading.