05 Nov 2012

Normalisation. Part 4

Category:
  • Software Design & Development
Third Normalised Form (3NF)

This is the last stage of normalisation, there are further levels but it’s the first three that are the integral ones.

This level of normalised form is the process of identifying transitive dependencies. As intimidating as this sounds, it really isn’t! These are attributes which depend on fields other than the primary key.

For example, within the customer table there is a field entitled discountAmount. This particular field is dependent on the membershipType and not the custNo. Therefore, these two fields will be put into a new table. The membershipType will become our primary key, every tables needs one.

Now we have a separate table, we need a way of linking it to the customer table, the membership type is a part of every customer. To do this we use a foreign key. A foreign key is always points to the primary key in another table. So we have membershipID in the customer table.

This idea can be considered a lookup table. Every customer has a membershipID, but to identify the discount amount we have to lookup the membershipID in the membership table to find the corresponding discount value.

This same idea applies to the staff. Staff details will be removed to their own tables and linked back to the Bookings table, where they originated from, using a foreign key. Remember the scenario stated that the airline wants to know which staff booked what flights.

So, our final normalised database looks like this:

Primary key

Foreign Key

Normaliastion SpreadSheet 4

So that’s normalisation in a nut shell. Like anything, practice makes perfect, the more you do it the more comfortable you will feel with the process. It is definitely worth spending some time on. Getting this stage correct really does maximise the potential of your Iron Speed web applications.

Go Back

Please enter your details and we will aim to call you back the same day.