Database Normalisation

You're currently employed as an IT support person at the local police station, and you nearly fall over when you are asked to do some data entry, only to find the following "database" being used to record traffic fines!?

DriversLicenseID	FineType	IncidentLocation	DriverName	FineAmount	Reporting officer	Date
26545LD	Speeding (-15)	Springwood	Harry Potter	$350	Jenny Rainer	12/5/05
65475TN	RedLight	Katoomba	Bob Smith	$200	Jeff Skillset	15/5/05
75163LD	Speeding (15+)	Lawson	Rachel Rogers	$350	Nadia Smith	20/5/05

What's wrong with this database? Can you see how it might be possible to enter inconsistent information?

Over the past few weeks, we've been practising the task of improving database tables by breaking them down into smaller tables with relationships. The technical term for the process that we've been practising is Normalisation. It's probably a good time now to find out a bit more about Normalisation before we tackle our Police Fines database. After walking around a bit, take 10mins to read this useful Microsoft Knowledgebase article: Database Normalization Basics. We'll go through the example there together, discovering 1st, 2nd and 3rd normal form.

We'll see if we can Normalize the database to come up with a much more efficient (and consistent!) design!