Ken Sheridan
Member
- Local time
- Today, 23:44
- Joined
- Jul 10, 2025
- Messages
- 86
As has been pointed out by others, the process of normalization is crucial to the design of tables in a relational database. To understand normalization it is firstly necessary to have a good understanding of the concept of functional determination, which is the basis of the principles on which normalization, as the term is most commonly used, is based. There are alternative normalization schemes which make no mention of functional determination, but I'm not concerning myself with those here.
Let's consider your original tblService table as an example. This includes the following columns:
ContactName
ContactPhone
ContactEmail
The ContactName table is functionally determined by the table's primary key, i.e. for each value of ServiceID the value of ContactName for the service in question is known. Moreover, it is known in this table only from the table's key. It is therefore functionally determined in this table solely by the whole of the key. On the basis of this column alone therefore the table would be normalized to Third Normal Form (3NF) whose formal definition is:
Third Normal Form: A relvar is in 3NF if and only if it is in 2NF and every non-key attribute is nontransitively dependent on the primary key.
The table would also be normalized to Boyce/Codd Normal Form (BCNF), whose formal definition is:
Boyce/Codd Normal Form: A relvar is in BCNF if and only if every nontrivial, left-irreducible FD has a candidate key as its determinant.
BCNF is a normal form which was introduced when it was found that, in certain circumstances, 3NF is deficient, particularly where a table has two overlapping two-column candidate keys. BCNF is a replacement for the first three normal, and unlike other normal forms does not require a table to be normalized to earlier normal forms, as with 3NF above. Informally the above definition for BCNF means that all determinants have to be candidate keys, so it's pretty easy to identify whether a table is in BCNF once the functional dependencies within the table have been identified.
I'll say more about the use of a personal name for the contact in this table below.
The ContactPhone and ContactEmail columns on the other hand would not be determined solely by the key, however, if only one email address and one phone number are stored for each contact. They would be determined by ContactName, so would be transitively determined by the key, and consequently the table would not be normalized to 3NF.
The ContactName column would be a foreign key referencing the primary key of a Contacts table. However, personal names can legitimately be duplicated, so cannot be used as keys. The column would have to be replaced by a numeric ContactID column referencing the numeric (probably an autonumber) primary key of Contacts.
What if, on the other hand, each contact could have multiple phone numbers, however, and each of those numbers could only apply to more than one contact? The numbers would be stored in a PhoneNumbers table with the PhoneNumber column as its primary key. The many-to-many relationship type between Contacts and PhoneNumbers would be modelled by a ContactPhoneNumbers table with two foreign key columns, PhoneNumber and ContactID, which would constitute the composite primary key of the table. The tblService table would then need to include a PhoneNumber, and the combination of ContactID and PhoneNumber would be a composite foreign key referencing the composite primary key of ContactPhoneNumbers. This would relate one valid phone number to each service record.
The table also includes the following column:
ContactLocationInfo
You are proposing to store multiple values in this column, so I assume that it is a Long Text data type. However, the attributes you are proposing to list in this are data. The column is therefore being used as a data structure. This is not a good use of a Long Text column, which is more suited to storing unstructured information. I and my American partner (she's an archivist) recently did this in a database of enslaved persons on the estates of a certain family in the USA, using the column to contain a free-text biographical narrative. In your case I would expect this sort of data to be recorded as values in columns of a set of related tables, one of which would be a Locations table whose primary key would be referenced by a LocationID foreign key column in tblService.
Using this one of your original tables as an example, I hope the above has given you an outline of the principles you need to apply when identifying the functional dependencies within a table, and from these determining whether the table is normalized. There are other normal forms beyond BCNF, but at this stage I would not concern yourself with these too much. Concentrate on the first three normal forms to start with. Most tables normalised to 3NF will be normalised to the higher normal forms.
Let's consider your original tblService table as an example. This includes the following columns:
ContactName
ContactPhone
ContactEmail
The ContactName table is functionally determined by the table's primary key, i.e. for each value of ServiceID the value of ContactName for the service in question is known. Moreover, it is known in this table only from the table's key. It is therefore functionally determined in this table solely by the whole of the key. On the basis of this column alone therefore the table would be normalized to Third Normal Form (3NF) whose formal definition is:
Third Normal Form: A relvar is in 3NF if and only if it is in 2NF and every non-key attribute is nontransitively dependent on the primary key.
The table would also be normalized to Boyce/Codd Normal Form (BCNF), whose formal definition is:
Boyce/Codd Normal Form: A relvar is in BCNF if and only if every nontrivial, left-irreducible FD has a candidate key as its determinant.
BCNF is a normal form which was introduced when it was found that, in certain circumstances, 3NF is deficient, particularly where a table has two overlapping two-column candidate keys. BCNF is a replacement for the first three normal, and unlike other normal forms does not require a table to be normalized to earlier normal forms, as with 3NF above. Informally the above definition for BCNF means that all determinants have to be candidate keys, so it's pretty easy to identify whether a table is in BCNF once the functional dependencies within the table have been identified.
I'll say more about the use of a personal name for the contact in this table below.
The ContactPhone and ContactEmail columns on the other hand would not be determined solely by the key, however, if only one email address and one phone number are stored for each contact. They would be determined by ContactName, so would be transitively determined by the key, and consequently the table would not be normalized to 3NF.
The ContactName column would be a foreign key referencing the primary key of a Contacts table. However, personal names can legitimately be duplicated, so cannot be used as keys. The column would have to be replaced by a numeric ContactID column referencing the numeric (probably an autonumber) primary key of Contacts.
What if, on the other hand, each contact could have multiple phone numbers, however, and each of those numbers could only apply to more than one contact? The numbers would be stored in a PhoneNumbers table with the PhoneNumber column as its primary key. The many-to-many relationship type between Contacts and PhoneNumbers would be modelled by a ContactPhoneNumbers table with two foreign key columns, PhoneNumber and ContactID, which would constitute the composite primary key of the table. The tblService table would then need to include a PhoneNumber, and the combination of ContactID and PhoneNumber would be a composite foreign key referencing the composite primary key of ContactPhoneNumbers. This would relate one valid phone number to each service record.
The table also includes the following column:
ContactLocationInfo
You are proposing to store multiple values in this column, so I assume that it is a Long Text data type. However, the attributes you are proposing to list in this are data. The column is therefore being used as a data structure. This is not a good use of a Long Text column, which is more suited to storing unstructured information. I and my American partner (she's an archivist) recently did this in a database of enslaved persons on the estates of a certain family in the USA, using the column to contain a free-text biographical narrative. In your case I would expect this sort of data to be recorded as values in columns of a set of related tables, one of which would be a Locations table whose primary key would be referenced by a LocationID foreign key column in tblService.
Using this one of your original tables as an example, I hope the above has given you an outline of the principles you need to apply when identifying the functional dependencies within a table, and from these determining whether the table is normalized. There are other normal forms beyond BCNF, but at this stage I would not concern yourself with these too much. Concentrate on the first three normal forms to start with. Most tables normalised to 3NF will be normalised to the higher normal forms.
