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.

Calculated fields are exactly what they sound like, fields created by a calculation. To make up, let’s open up the Kmeans Table. Go to the Table Fields tab and click on More Fields > Calculated Field > Number

Select AVG Labor and Labor Cost from the Expression Categories. Let’s multiply the two values, put a * in between them

A new field will appear, you can rename the field if you want.

If you look at the table in Design view, you can examine the calculated field another way

Back to Main Course Page: Course

Queries are how you search and return data from a database. In this example we will be using data loaded into Access from my first two Access Lessons:

• Lesson 1
• Lesson 2

If you have the tables loaded, lets move onto the Query Wizard. Click on Create then Query Wizard

Select Simple Query Wizard and Okay

Select Kmeans as your table

Select the >> to move all the fields over to Selected Fields Window

Hit Next > Leave Detail selected and hit Next

You can title your query if you want. Then hit finish

Your results with now appear

Right click on your query in the side bar and select Design View

Filtering Query

With Design View open, lets uncheck the ID column and in the Criteria Spot under Model, type in “IE33” with quote marks

Now click on your query in the left bar and your results with appear

Right click query and go back to designer. Change “IE33” under Model to “A*” (* are wildcards meaning return anything that starts with A

Double click Kmeans Query and view results

Back to Main Course Page: Course

The goal of good database design should be to provide a solution to real world problem. In order to create a solution though, you first need to to understand the problem at a granular level.

One approach that I like to take:

• Break down tasks users perform
• Ensure you have the data needed for those tasks

Keep in mind:

•Understanding a problem involves looking at both the input and output tasks

•The data model goes hand in hand with the use cases

•Continue to re-evaluate whether the data model can handle the input and output use cases

•Most of the time, users won’t have this readily available and it needs to come through discussion

•Users can be very narrowly focused, it’s your job as the designer not to be

Let’s imagine an example of a meal delivery service where people call in to a receptionist who takes the order and dispatches drivers to delivery the meals

We need to figure out:

1.Who are the users?

2.What tasks do they do?

3.What data is needed?

---

First make sure you understand the process:

Users and Tasks:

•Visitor calls the company

•Receptionist takes the order

•Receptionist selects/assigns a driver

•Receptionist gives order to the driver

•Driver picks up meals

•Driver delivers meals

•Visitor pays the driver

•Driver informs the company

•Receptionist records the completed order

•Driver maintains timesheet

---

Then decide what tasks our system will be involved with?

Users and Tasks:

•Visitor calls the company (probably not)

•Receptionist takes the order (yes – need to record some details)

•Receptionist selects/assigns a driver (yes – need to see who is available)

•Receptionist gives order to the driver (yes – need to be able to see the order details)

•Driver picks up meals (maybe – do we need to keep track of time of pickup?)

•Driver delivers meals (maybe – do we need to keep track of delivery time?)

•Visitor pays the driver (probably not)

•Driver informs the company (maybe – is the driver entering details himself or calling?)

•Receptionist records the completed order (yes)

•Driver maintains timesheet (yes)

---

Then we can create some basic tables and relationships based on this imformation above

The Driver and Order interact as the driver needs to get an order to deliver, and the driver and timesheet interact as the driver needs to fill in the time sheet, but the Timesheet and Order do not interact though, so there is no relationship there.

Next, we can fill in some attributes

This basic model above should meet the needs of the use case described

Sample of steps to take when working through the analysis phase:

•What does the user do?

•What data is involved in the user’s jobs?

•What is the main objective of the system?

•What data is needed to satisfy the main objective?

•What are the input use cases?

•What is the data model? 

•Can it handle everything so far?

•What are the output use cases?

•Can the data model provide everything so far?

Back to Main Course Page: Course