Database — Database Design: Conceptual Design (Part 4)
The conceptual design provides a high-level description that’s close to the way many users perceive data.
Wish you already came along the last part Database — Design Process (Part 3)
We’ll start by identifying the tables, their columns, and their relationships with other tables.
Identifying The Tables and Relationships
Identify Tables
As we’ve mentioned earlier, a database consists of tables, each table has related information.
You start identify the potential objects from your requirements. Objects could be anything; customers, students, courses, orders, employee, department, project, and so on. Objects also could be things that doesn’t exist in the real word, things like the comments in your blog, the posts, categories, and so on.
These objects will be your tables. But, you need first to refine your chosen objects. “How can we do that?”, here you are:
- Remove any duplicates, you may find same objects with different names, but they actually do the same thing.
- You may need to combine some objects or even splitting them into some other objects.
- You may identify an attribute(column) as an object instead. An attribute is a piece of info that’s related to a specific object.
⚠ Conflict: Working In An OO Language
Take care if already are doing a similar kind of process for your application, and you’re working in an object oriented language, because you often ask similar kinds of questions.
Sure, there is some crossover here, for example, in a sales application, we are likely to have both an order table defined in my database and an order class defined in my application, but they are different.
In the relational database, we are concerned about the data that needs to be saved. Our focus is not on things like methods and behavior, or inheritance, polymorphism, like it might be in an object oriented language.
So, we’re concerned here just about our tables, and their relationships.
Identify Relationships
Next is to identify the relationships between those tables. You have a customer places orders, or an employee works in a department.
You can use the symbol “1-M” to denote one-to-many relationship, and “M-N” for many-to-many relationship, and also “1-1” for a one-to-one relationship.
Remember? These types of relationship (one-to-many, many-to-many, or one-to-one) are constrains that specify the maximum participation between tables.
Identifying The Columns and Their Data Types
After identifying the tables in our application, now it’s time to determine what’s the data needs to be in each table.
A column, or an attribute defines every piece of information. It defines the name and the type for each piece of data in a table. For example, an employee table may have name, address, salary, …etc.
Then, determine the data type for each column. Is it text or character data, or is it numeric? Is it a date, a time, or even binary data like an image, or a piece of audio or video?.
There are many data types, and this depends on the DBMS you are going to use, but, when you get into using a specific DBMS, you’ll just have a summary of all the available data types until you get to know them.
Data types in a database are not the same as in a programming language.
Length
Columns like name, and address, we need not to just specify it’s data type, but also specify if it should be with a fixed length, or variable length.
Null
Another option is to specify whether the column should always have a value, or not. Maybe it’s fine if we didn’t insert the address of a specific employee, but, it’s impossible to insert an employee without a name.
In this situation, you can choose either the column should be NULL in case there is no values inserted, or NOT NULL so it must have a value.
Usually the DBMS will insert the default value in case you didn’t insert any value to a column that’s defined as NOT NULL.
Default Values
Do you want a default value to be inserted for a specific column in case you didn’t. For example, an employee could have a default value set to “0.0” in the salary column in case we didn’t insert it.
Other Options …
We might add as well some other constraints. For example, we may force a column value to match a specific pattern, like matching an email address or a phone number or a credit card number.
DBMS wants to know these specifics so it can be efficient about storing and indexing them, and enforce your rules on those columns, that your data will stay valid and consistent.
— Primary Keys
If you are following along from the previous tutorials, you should know what’s the primary key. Now, you choose which column will be the primary key. You may have a primary key that’s naturally exists as we discussed before, or ask the DBMS to generate it for you.
It’s typically an integer number. You can also have this primary key as “auto-increment”; means every time you insert a row, the primary key will be generated and incremented automatically, hence no need to insert it.
Sometimes it’s more convenient to generate a primary key (system-generated), like an employee id even if you do have a piece of real data that uniquely identifies a row, like a social security number … Why?, because this is a number that could be passed around in emails or printed on an ID card, unlike a sensitive piece of data, like a social security number.
Composite Primary Keys
Another option for the primary keys is that you can have more than one column together they form a primary key. This is when one value does not uniquely identify a row, but two (or more) values do.
You have already seen this in many-to-many relationship, when we created a “linking table”. It has two foreign keys from the participating tables, and the two foreign keys; course id and student id are together form a primary key together.
It means, a student can have more than one course, and a course can be taken by one or more student, but there can’t be a row with the same student id, and the same course id.
Now, it’s true that it might sometimes be more useful or even more convenient to generate a primary key column (by the DBMS) anyway.
Database, Table & Column Names
Now you might be asking, “What’s the best common way used to name my tables and columns?”. Actually it depends on you, some people prefer Pascal case; upper case first letter with singular nouns for database, tables, and columns.
And some other prefer the lower case letters with underscores (plural nouns) for tables, and lower case letters with underscores (singular nouns) for database and columns.
The thing is, just choose one convenient way for you, and stick to it.
Graphical Tools
All you really need to model a database, at least initially, is pencil and paper, although there are some tools you can use, like Visio from Microsoft.
The tables, columns, and their relationships could be sketched using “Entity Relationship Diagram (ERD)”, which will be covered in the next tutorial.
Wrapping Up
After identifying your table, columns, data types, relationships, and primary keys. This takes us to the next step in the database design, which is the “Logical Design”.
But, before diving into it, we are going to give a quick tutorial about the “Entity Relationship Diagram (ERD)”.
