A relational database holds its data in tables. These tables may be linked together, using common values within them. The tables are not permanently linked together. Each table is an independent data store – but the database system is capable of linking the data together – when required.
Generally speaking, each table contains data relating to a particular subject, for example, customer details could be held in one table, and the orders placed by the customers in a separate table. If you wished to see all the orders for a particular customer, the tables could be linked.
Failure to use normalised tables may result in a loss of data integrity. To this end, you should examine your tables and proceed through the normalisation process to try to achieve third normal form (3NF).
First Normal Form – 1NF
A table is in 1NF if and only if every non-primary key is contains only one entry.
Second Normal Form – 2NF
This is relevant for tables with multi-field primary keys. The table is in 2NF if it is in 1NF and every non-primary key is only dependent on the whole primary key. In other words, you must not be able to determine values in the rest of the table from any subset of the primary key.
Third Normal Form – 3NF
A table is in 3NF if it is in 2NF and every non-primary key is dependent only on the primary key. Therefore, one non-key field must not be dependent upon another non-key field.
Relational Databases and Normalisation
Relational database resolve the above normalisation issues by having separate tables for details that could be repeated within tables.
Microsoft Access 2007 and 2010
Since Access 2007 there has been a new data type in Access tables, which is the multi-value field. This new data type was introduced to allow Access to work with SharePoint. The multi-value field in theory breaks the normalisation rules discussed earlier. However, behind the scenes Access is still applying the normalisation rules by using hidden interim tables. However, writing queries for access databases that use tables with multi-value fields is fraught with problems. I’ll take a look at these problems in a future blog.