A data model never intends to result in a perfect or complete relational design, but to reach an agreeable state that accurately reflects the essential requirement of the real world problem. Models are basically an abstraction of a situation at which we arrive after numerous iterations of trial and error. These methods reflect our improved understanding and the changing scope of the scenario. After arriving at a comfortable state, we move on the phase called database design. The article shall try to provide some clues to creating a relational design from scratch and outline the trip through data models to relational database design.
Use Case Scenario
Before creating a data model, a practical way to get a feel of what the data will involve is to create a use case diagram. Let’s create a simplistic use case scenario.
Figure 1: A simple use case scenario
Employee works for department, and department controls the projects. Each department is headed by a manager and each project would have one or more employees working on it.
Classes and Their Attributes
Now that we have an overview of the problem, we can be a little more abstract and form a class diagram. Class diagrams are a useful way of representing information and giving an idea of the attributes of the entity. A class is basically a template for storing data about a set of similar things. As is obvious from the preceding use case, we would have at least three classes. Let’s create our initial three classes with their attributes and data types.
Figure 2: Three classes with their attributes and data types
A data model is conceptual tool that describes the structure of the database, data relationships, semantics, and consistency constraints that should hold on the data. Also, it includes the basic operations such as retrieval of data and update on the database. Data models can be grossly categorized into conceptual and physical models. The conceptual model is concerned about the user perception of data and its logical representation. the physical model, on the other hand, describes the detail about how data is stored in the computer at a very low level. A database purist may further granularize the category, but for the sake of simplicity, we’ll not delve into the jargon much and focus more on getting things done.
Representing the Model
The most common and simple model is the Entity-Relationship model (ER model/diagram) where we describe the entities and their relationship with other entities of the system based on the perception of a real world problem. It is basically a diagram to establish a conceptual base of the relational design. Database designers commonly pick up their pen first to draw the ER diagram. The rule of thumb is to identify entities of the proposed system and create relationships among them, such as shown in Figure 3.
Figure 3: An Entity-Relationship (ER) model
The rectangular shapes represent the entities, oval shapes its attributes, and the diamond shapes the relationships between entities. The cardinality of related entities are mentioned by the symbol 1..N, for one to many relationship, 1…1, for one to one relationship. That means:
- One employee may work for many departments.
- One employee may work in many projects.
- One employee manages one department.
- One project is handled by many departments.
Classes and Attributes with Relationships
We can refine our rudimentary class diagram with relationships and their cardinality. Representing the data model with classes and attributes gives another dimension to our perspective. From the view of object oriented programming, this give a thorough idea about the class object and relationship we are to implement, especially when we are planning to work on a ORM (Object Relational Mapping) framework.
Figure 4: An Object Relation Mapping (ORM) framework
This is the culmination of everything that’s been said so far. A relational database enables us to create tables using a language called SQL (Structured Query Language). The same language can be used for manipulating data as well. Some database packages provide a GUI front end to define tables and their relationship, but here we’ll limit ourselves to SQL only. So, let’s create the tables according to the ER model we have created.
CREATE TABLE Employee ( emp_id INT UNSIGNED, first_name VARCHAR(30) NOT NULL, mid_name VARCHAR(30), last_name VARCHAR(30) NOT NULL, birth_date DATE, salary FLOAT(10,2) UNSIGNED, address VARCHAR(50), dept_no INT UNSIGNED NOT NULL, PRIMARY KEY(emp_id), FOREIGN KEY(dept_no) REFERENCES Department(dept_no)); CREATE TABLE Department( dept_no INT UNSIGNED NOT NULL, dept_name VARCHAR(30) NOT NULL, manager_id INT UNSIGNED NOT NULL, manager_start_date DATE, PRIMARY KEY(dept_no, dept_name), FOREIGN KEY(manager_id) REFERENCES Employee(emp_id)); CREATE TABLE Department_locations( dept_no INT UNSIGNED NOT NULL, dept_location VARCHAR(20) NOT NULL, PRIMARY KEY(dept_no, dept_location), FOREIGN KEY(dept_no) REFERENCES Department(dept_no)); CREATE TABLE Project( proj_no INT UNSIGNED NOT NULL, proj_name VARCHAR(20) NOT NULL, proj_location varchar(20), dept_no INT UNSIGNED NOT NULL, PRIMARY KEY(proj_no, proj_name), FOREIGN KEY(dept_no) REFERENCES Department(dept_no)); CREATE TABLE Employee_project( emp_id INT UNSIGNED NOT NULL, proj_no INT UNSIGNED NOT NULL, hours FLOAT(4,2) NOT NULL, PRIMARY KEY(emp_id, proj_no), FOREIGN KEY(emp_id) REFERENCES Employee(emp_id), FOREIGN KEY(proj_no) REFERENCES Project(proj_no));
When creating a relational design, finding a starting point is a big problem faced by novices. The article provided some of the common steps followed when designing a database from scratch. Because we were trying to keep things simple, the article omitted steps to normalize and optimize relations before actually implementing them in SQL. However, bear in mind that there is no hard and fast rule for a good design. In fact, the art of good design comes from experience and great design from intuition and the way to reach any one of them is through practice. I have found database design is simpler when we apply more common sense than rules. Take the scenario discussed in this article as the tools to apply your common sense rather than rules to follow.