Designer
for Microsoft Access
Create complex MS Access databases without being an expert in relational
database design! Designer for Microsoft Access asks you plain-language
questions about what you want to manage with your database, and creates
the tables and relationships automatically. Free trial available
Once you have decided on your initial attributes (database fields), you may need to look into how to break this attribute down, a process more commonly known as decomposing attributes. Decomposing an attribute takes the attribute from its original form and divides it into its own components.
A simple example of this might be the breaking down of an Address attribute. An Address might easily be broken down and defined using the following attributes to store all of its data:
This process of decomposing the attribute helps you develop a fully normalized database structure. Decomposing is also a function of usage as well.
If, for example, an Employees name is only ever needed in it's fullest form, then the developer may take the decision to not break this up into attributes of Employee First Name and Employee Last Name.
Decomposing an attribute provides many benefits to an attribute that has not undergone the process of being decomposed.
First off, Data Integrity is improved.
Data Integrity is a measure of how well data is kept consistent and flawless throughout the whole ER model.
When attributes are decomposed, different methods of ensuring data integrity can be applied to the broken down segments of data rather than to the attribute as a whole. For example, a House Number can be checked as an integer and the Town as a string. Secondly, decomposing aids in the sorting and improving data retrieval performance.
A generic attribute of Address contains a House Number, Street Address, Town, County and PostCode. To enable you to locate the the Town of Bournmouth in Dorset, you have to select the whole address attribute and parse this to find Dorset, thereby degrading performance due to the redundant data being retrieved.
If you have five separate attributes, you can easily select the Town column and retrieve your results much more quickly. This same rule applies to updating your data. It is far easier to update a single part of the Address rather than parsing and then updating the whole Address attribute.
In most cases, an attribute is identified for each entity that will act as a key. This attribute could be an Employee Number, Product Code or simply an attribute to serve no other purpose than to act as a key identifier. When an attribute is added solely for the sake of being an identifier, it is known as a surrogate key. Whether you use a natural or surrogate key, that will represent an important part in establishing relationships. The term Primary Key is assigned to this attribute. Even though some RDBMS do not mandate the use of a Primary Key, one should be defined simply as a matter of good practice.
Additional Reading:
Relational Database Normalisation Process: Illogically or inconsistently stored data can cause a number of problems. In a relational database, a logical and efficient design is just as critical. A poorly designed database may provide erroneous information, may be difficult to use, or may even fail to work properly.
What is a Key and How should I choose one?: Keys are crucial to a table structure for many reasons, some of which are identified below...