Normalisation is the term used to describe how you break a file down into tables to create a database. There are 3 or 4 major steps involved known as 1NF (First Normal Form), 2NF (Second Normal Form), 3NF (Third Normal Form) and BCNF (Boyce-Codd Normal Form). There are others but they are rarely if ever used. A database is said to be Normalised if it is in 3NF (or ideally in BCNF). These steps are descibed as follows:
Note: When attribute is used we are speaking of a field in the table
1NF
To put a database in 1N
In a database a table on Customers would have an address attribute. The address is made up of Company Name, Address Line1, Address Line2, Address Line3, City, Postcode. There are 6 values to this address and as such each should have it's own field (column).
If your company sold furniture a table on products could have a description attribute. If for example that attribute was 'Beech Desk 120w x 75h x 50d'. Ideally this would be broken down into a number attributes like 'Colour', 'Type', 'Width', 'Height' and 'Depth'. The reason for this is it would allow you to seach the database for all Desks, for all pieces of Beech furniture, for all desks with a width of 120 etc.
In a general Invoicing database you would have a separate table for Customers, Orders, Products, Invoices and you would probably need tables for OrderDetails and InvoiceDetails as well. Each of these tables must have their own primary key. Each of these tables except for customers would have a foreign key reference to the primary key of another table. (See Relationships below)
(Underlined fields are Primary Keys and Italicised fields are Foreign Keys)
In a customer order you could have more than one product. That is the customer has ordered more than one item. If you tried to put all of this in one table as {OrderID, CustomerID, OrderDate, Product1, Product2, Product3} what would happen if the customer ordered more than 3 products. There would also be implications for querying the kind or quantiy of products ordered by a customer. Therefore these product fields don't belong in the order table which is why we would have an OrderDetails table which would have a foreign key reference to the Orders table {OrderDetailsID, OrderID, ProductID, Quantity}. Using productID as a foreign key to the product table means you don't have to identify the product attributes here. This also allows you to enter a quantity figure for the product ordered.
Relationships:
All tables should have a 1 to 1 or 1 to many relationship. This means for example that 1 customer can have 1 or many orders and 1 order can have 1 or many details.
Therefore Orders table would have a foreign key reference to the Customer table primary key {OrderID, CustomerID, OrderDate} and the OrderDetails table would have a foreign key reference to the Order table primary key {OrderDetailsID, OrderID, ProductID, Quantity}. This table also contains a foreign key reference to the Products table. As a product is likely to be ordered more than once there is a many to 1 relationship between the OrderDetails and the Products table.
If any tables have a many to many relationship this must be broken out using a JOIN table. For example, Customers can have many Suppliers and Suppliers can supply to many Customers. This is known as a many to many relationship. You would need to create a JOIN table that would have a primary key made up of a foreign key reference to the Customers table and a foreign key reference to the suppliers table. Therefore the SuppliersPerCustomer table would be {SupplierID, CustomerID}. Now the Suppliers table will have a 1 to many relationship with the SuppliersPerCustomer table and the Customers table will also have a 1 to many relationship with the SuppliersPerCustomer table.
2NF
The database must meet all the requirements of the 1NF.
In addition, records should not depend on anything other than a table's primary key (a primary key can be made up of more than one field, only if absolutely necessary like in a JOIN table).
Example:
A customers address is needed by the Customers table, but also by the Orders, and Invoices tables. Instead of storing the customer's address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.
3NF
The database must meet all the requirements of the 1NF and 2NF.
The third normal form requires that all columns in a relational table are dependent only upon the primary key. A more formal definition is:
Example:
The Customer table contains information such as address, city, postcode imagine it also contained a column called shipping cost. The value of shipping cost changes in relation to which city the products are being delivered to, and therefore is not directly dependent on the customer even though the cost might not change per customer, but it is dependent on the city that the customer is in. Therefore we would need to create another separate table to hold the information about cities and shipping costs.
BCNF
A relation is in Boyce-Codd Normal Form (BCNF) if every determinant is a candidate key. BCNF is very similar to 3NF but deals with dependencies within the primary keys. BCNF in it's simplist terms just says don't have a primary key made up of more than one field unless it is a join table to disperse a many to many relationship and only contains the two primary keys of the tables it is joining.
Most relations that are in 3NF are also in BCNF. It only happens that a relation which is in 3NF is not in BCNF when the primary key in a table is made up of more than one field and the other columns are not dependent on both fields but only on one or the other.
A database is said to be normalised if it is in 3NF and/or BCNF
Notes:
Someone asked why normalisation is important
Note: When attribute is used we are speaking of a field in the table
1NF
To put a database in 1N
- ensure that all attributes (columns) are atomic (which means that any single field should only have a value for ONE thing).
If your company sold furniture a table on products could have a description attribute. If for example that attribute was 'Beech Desk 120w x 75h x 50d'. Ideally this would be broken down into a number attributes like 'Colour', 'Type', 'Width', 'Height' and 'Depth'. The reason for this is it would allow you to seach the database for all Desks, for all pieces of Beech furniture, for all desks with a width of 120 etc.
- Create a separate table for each set of related data and Identify each set of related data with a primary key
In a general Invoicing database you would have a separate table for Customers, Orders, Products, Invoices and you would probably need tables for OrderDetails and InvoiceDetails as well. Each of these tables must have their own primary key. Each of these tables except for customers would have a foreign key reference to the primary key of another table. (See Relationships below)
- Do not use multiple fields in a single table to store similar data
(Underlined fields are Primary Keys and Italicised fields are Foreign Keys)
In a customer order you could have more than one product. That is the customer has ordered more than one item. If you tried to put all of this in one table as {OrderID, CustomerID, OrderDate, Product1, Product2, Product3} what would happen if the customer ordered more than 3 products. There would also be implications for querying the kind or quantiy of products ordered by a customer. Therefore these product fields don't belong in the order table which is why we would have an OrderDetails table which would have a foreign key reference to the Orders table {OrderDetailsID, OrderID, ProductID, Quantity}. Using productID as a foreign key to the product table means you don't have to identify the product attributes here. This also allows you to enter a quantity figure for the product ordered.
Relationships:
All tables should have a 1 to 1 or 1 to many relationship. This means for example that 1 customer can have 1 or many orders and 1 order can have 1 or many details.
2NF
The database must meet all the requirements of the 1NF.
In addition, records should not depend on anything other than a table's primary key (a primary key can be made up of more than one field, only if absolutely necessary like in a JOIN table).
Example:
A customers address is needed by the Customers table, but also by the Orders, and Invoices tables. Instead of storing the customer's address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.
3NF
The database must meet all the requirements of the 1NF and 2NF.
The third normal form requires that all columns in a relational table are dependent only upon the primary key. A more formal definition is:
- A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key.
Example:
The Customer table contains information such as address, city, postcode imagine it also contained a column called shipping cost. The value of shipping cost changes in relation to which city the products are being delivered to, and therefore is not directly dependent on the customer even though the cost might not change per customer, but it is dependent on the city that the customer is in. Therefore we would need to create another separate table to hold the information about cities and shipping costs.
BCNF
A relation is in Boyce-Codd Normal Form (BCNF) if every determinant is a candidate key. BCNF is very similar to 3NF but deals with dependencies within the primary keys. BCNF in it's simplist terms just says don't have a primary key made up of more than one field unless it is a join table to disperse a many to many relationship and only contains the two primary keys of the tables it is joining.
Most relations that are in 3NF are also in BCNF. It only happens that a relation which is in 3NF is not in BCNF when the primary key in a table is made up of more than one field and the other columns are not dependent on both fields but only on one or the other.
A database is said to be normalised if it is in 3NF and/or BCNF
Notes:
Someone asked why normalisation is important
"Database normalization is a rather arcane topic that you will not be able to really understand until you work your way a little further into the database design process.
Simply put the rules of db design were developed years ago by some mathematicians working in things like 3rd order predicate logic, etc. Access and other db programs are built with these rules in mind. Yes, you can fudge your way around the way these rules/programs are built, but you do so at considerable cost to your brainpower, frustration levels, etc... Some things also not only become much more difficult to do, they can become downright impossible! A much simpler (and far more productive and safe) method is to simply accept that the rules are there for a reason.
As you work on db's more, you will begin to learn WHY the rules are there.
One simple example using your scenario: Suppose you store everything in one big table. Then next year, Quebec decides it doesn't want to be part of Canada anymore (yeah, sure... never happen...). How are you going to change the values in your 'one big table' that refer to Quebec? You will have to write code to search the fields to find each instance of the word Quebec and change it to whatever it gets changed to.
If, instead, you have a normalized db, the change is quite simple: you go to the table named something like tblProvinces, and make one change to the entry Quebec which then affects every entry that it is related to through table relationships. This concept is called data integrity, and is far more important than this simple example illustrates...
Change the example to involve accounting systems and $'s and you will understand perhaps more of the potential errors involved.
For example if you are storing employee salaries in one big table that has all information in it. How are you going to find salaried employees of a certain level of salary? Write code to search the fields to find the instances between a certain $ figure... How are you then going to change each employees salary when they get a raise? How are you going to generate a report to make sure that someone isn't (horrors) making more money than their experience/skill level?
It's only possible to dumb this subject down to a certain point! If you weren't willing to learn how to do it right, you wouldn't be here at this site asking for pointers, so I hope the above information is some help!"
No comments:
Post a Comment