Database Design: The Model

When discussing database design, the Model refers to the mock up we create for handling our data. This can be just a simple diagram you draw our or in larger databases, multiple pages of inter-related ERD (Entity – Relationship – Diagrams)

Often times, the model is developed in response to a use case or real world problem. So we are going to look at couple of examples here and see how a model is made to solve a real world problem:

First one, let’s look at a hotel geared towards groups of high school students. They want a way to keep track of which rooms the guests are staying in, as well as what group the guests belong to. They also want to have in the database a way to track which adults are associated with each group

So we create 3 classes, Group, Guest, and Room. Both Room and Group can only exist once. Room 2 is a Room 2, you can’t have 2 Room 2s.

Multiple Guests can be part of a Group though

One quick to identify problem with this model is, what happens if one person from a group leaves early. This model can not handle that exception

Optionality and Cardinality

Optionality and Cardinality are related to how many instances of an item can exist in Class (or table once its in the database)

Optionality is the smallest number of items that can exist. It represented by the first number in the grouping.

Cardinality is based on how many total objects the other side can associate with.

In the case below, A student can enroll in multiple courses and a course can have multiple students into. At the same time, a student could be enrolled in 0 courses, and it is possible to have a instance where there are no students in the school at all – so the Optionality and Cardinality will look like this.

What we have created here, is a many to many relationship. That is due to the fact that both students can be in more than one course and courses can have more than one student.

To get around the problem of many to many relationships, database designers need to implement intermediate classes or (lookup tables). This creates Course and Student classes that will not have duplicates, and an intermediate class that allows you to keep track of all the iterations of student and course combinations

With a Start and End date added to the Roster class, you now have an historical class that can track when a student took a particular class, even solving the issue of what happens if a student has to take the class again, or if the student drops the class.

Leave a Reply