473,695 Members | 2,302 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Database Normalization and Table Structures

MMcCarthy
14,534 Recognized Expert Moderator MVP
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
  • ensure that all attributes (columns) are atomic (which means that any single field should only have a value for ONE thing).
Examples:

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.
  • Create a separate table for each set of related data and Identify each set of related data with a primary key
Example:


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
Example:
(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 quantity 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 SuppliersPerCus tomer table would be {SupplierID, CustomerID}. Now the Suppliers table will have a 1 to many relationship with the SuppliersPerCus tomer table and the Customers table will also have a 1 to many relationship with the SuppliersPerCus tomer 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:
  • 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.
In other words, all nonkey attributes are functionally dependent only upon the primary key. All 3NF really means is that all fields (attributes) should be dependent on the tables primary key. If they are not they should be put in their own table. This means that every attribute unless it is a primary or foreign key must be DIRECTLY dependent on the Primary Key of this table and not on some other column.

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. One of our experts Scott Price posted a very useful reply to this in post #15
Jan 8 '07 #1
76 272045
debasisdas
8,127 Recognized Expert Expert
Thanx

Its a really helpful article.
May 4 '07 #2
MMcCarthy
14,534 Recognized Expert Moderator MVP
Thanx

Its a really helpful article.
You're welcome :)
May 4 '07 #3
Motoma
3,237 Recognized Expert Specialist
Unequivocal thanks for posting this article, Mary.
May 15 '07 #4
srinivasarao yarru
4 New Member
i am a learner so it is very helpful.
Jun 1 '07 #5
MMcCarthy
14,534 Recognized Expert Moderator MVP
i am a learner so it is very helpful.
I'm glad to hear it.

If you have any questions post them in the Access forum. We will do what we can to help.

Mary
Jun 1 '07 #6
MMcCarthy
14,534 Recognized Expert Moderator MVP
Unequivocal thanks for posting this article, Mary.
No problem.

You're welcome.
Jun 1 '07 #7
debalina
2 New Member
Thank U Very Much For The Article ... It Is Really Helpful For Me ..
Jun 16 '07 #8
MMcCarthy
14,534 Recognized Expert Moderator MVP
Thank U Very Much For The Article ... It Is Really Helpful For Me ..
Thank you.

I'm glad you found it helpful.

Mary
Jun 16 '07 #9
KevHill
6 New Member
Very nice, it would be good to add something about the rationale of why you would want something normalized. I assume it ease of upkeep and efficiency of searches, etc.
Jun 25 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
2639
by: Dinesh Garg | last post by:
Hi All, I want to use MS sql server edition 2000. I have installed the server. Now i want to create the database on this server with tables and triggers. Can please someone suggest me how to do this ? Thanks a lot, Dinesh
1
1989
by: Arjen | last post by:
Hello, If I choose to use a database then I need to use multiple tables. With a XML file I can select "my objects" as once. I think that there will be around 10.000 records (objects). I want to select my objects on different (selection criteria) ways (on attributes). And I want to add, update and delete my objects. Can somebody tell me if it is a good idea to use a XML file as database or
2
4938
by: Shwetabh | last post by:
Hi, I am using MS-SQL server to store my database. My problem is that I have around 150+ database files in DBF format. Each database file consists of fields ranging from 2 to 33 in number. Also, there are some fields which have just one entry and rest are NULL. This database will be accessed by a printing software. Please advice as to how I should proceed to normalize this database. Regards,
2
2260
by: kkant | last post by:
Hi , I am KKant from Bangalore, India. I have a jos to do, I have to store VB codes in a a Database (Access) table and in Run time I have to read those codes and Exute them. But I dont know how to ? Please help
4
2151
by: DaveP | last post by:
Hi im looking for a Example of getting the table structures back to my app..... TIA Dave P
3
3230
by: ago | last post by:
Hi, Is there any way to compare two identical table structures in access for different values in them. EG: Table 1: Name occupation rob plumber Table 2: Name occupation
1
2963
pbmods
by: pbmods | last post by:
Heya. I'm adding features to a pre-existing project, and I was curious about the way I've been going about designing my database additions. My methodology is to keep my stuff separate from the other guy's stuff as much as possible. That way, I know that if it breaks, it is definitely my code's fault (or at least, 85% of the time it is... who HIRES these people?!). Likewise, on the database side, there's a pre-existing CMS in there, and...
1
2068
by: Rizki | last post by:
hai i want to make the update form for my website, and in my update form there is two combo box for chose where database you want to input the data and where table do you want to fill the field, for eaxample: _______________ chose database to save |___________|_| _______________ chose table to save |_____________|_| if i chose database "example1" table "field1" then it will input...
0
8635
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8574
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8994
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8830
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7664
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6493
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5839
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4342
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3008
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.