Normalization
Designing Database Tables – Part 1
Division 2
Forward: This part of the series, introduces you to the techniques of Designing database tables. I assume that you have read the first division or similar text, before reading this division.
By: Chrysanthus Date Published: 4 Aug 2012
Introduction
A database is made up of related tables. I showed many tables in the previous division. You will still see more tables in the next parts of the series.
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Business Rules
Imagine that you are selling in a supermarket. In one sale there can be many products. That is an example of a business rule. Important business rules are one-to-one and one-to-many relationships. There are a few other business rules we shall see at the end of the division. Business rules just mean the rules you use to run your business. There are even some business rules that you as a businessman uses without being conscious that they are business rules. The thing is that, with computers everything has to be coded (programmed). In the coding process, you are conscious of every business rule that has to be coded.
Let us recall certain things before we continue.
Keys
A Primary Key is one column or a set of columns that uniquely identify rows of a table. When a primary key is made up of a set of columns, it is called a Composite Key. A Surrogate Key is a forced single column primary key. This forcing is normally done by the computer (DBMS) and the key column is normally made up of counting numbers, e.g 1, 2, 3, 4, etc. or 10, 20, 30, 40, etc. A Foreign Key is a column in one table, which is a primary key in another table. In theory nothing stops a foreign key from being made up of two columns; in such a case you would have a composite foreign key.
A class is a table without rows or a table with empty rows. In practice you will not have a table with empty rows. I just say this so that you appreciate the meaning of a class. You will meet classes in a kind of programming, called Object Oriented Programming (OOP). When a table has rows, and each of the rows has data, each of such rows is called an object. The names of the columns of a table are called properties of the class (table) or properties of the objects (rows). Another name for property is attribute.
If you have studied any object Oriented Programming, you may ask, “how do methods (member functions) fit into the database tables?” In the previous division I was mentioning the phrases and words like, “computer system”, “programs”, “program code” and “coding”. I did not really want to use the word, “function” or “method” since you might not have studied programming as to appreciate what “function” or “method” means.
A function is a small piece of programming code that carries out a task. A task may mean a simple thing like to add two numbers together. The more involving (complex) the task is the larger the function (code). A relational database in a computer use functions, to carry out tasks that deal with the tables. With an object database these functions are attached (code-wise) to the tables. Because of that attachment they are called methods. Object database looks at tables as classes with properties and methods.
Object Databases are relatively new in the market and many companies are still to start using them. This series focuses on relational database. I advice you to learn relation database first before you learn object database because of the following reasons: relational and object databases are solving the same problems or dealing with the same things. Object databases are still to have their roots in companies. The block diagrams in the design of object database are similar to the diagrams we have seen in the previous division. There is no clear cut between object databases and relational databases.
When designing tables in a database, the following criteria should be met:
- The tables should be as small as possible in terms of number of columns. As a result the total tables will occupy a small space in the computer hard disk (and on paper). The next criterion gives the reason for this.
- Duplication of data should be minimized. This means you should not allow a row or a portion of a row, to repeat itself as a complete row or a row potion in another table. Keys should be used to solve this problem.
- Data should be protected to ensure its consistency. If the data are not well kept there would be errors: in one table you may have one value for a datum, in another table you may have a different value for the same datum, or the datum may be the same all over but wrong. This situation must be avoided.
If a table meets the above criteria, then it is said to be in a Normal Form. Establishing tables in normal forms is called Normalization. There are two common ways of doing this. In practice most designers would use one way, and then use the other way to check and finalize their tables.
Wow, all that is good. Well, this is a good point to take a break and begin the next part of the division with one of the ways.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT