Database Normalisation
-
I'm currently in the process of creating a service records database for storing information on machine servicing. In the un-normalised customer table I have three attributes for recording telephone contact details: LandlineNumber, MobileNumber and FaxNumber. Ordinarily, these attributes should be decomposed through the normal forms; 1NF, 2NF and 3NF. TELEPHONE_1NF(TelNumber, CustomerNumber, TelNumberType) TELEPHONE_2NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_2NF(TelNumber, TelType) TELEPHONE_3NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_3NF(TelNumber, TelType) My question is, in the real world, do you database designers out there ACTUALLY do this, or would you leave the three different telephone attributes in the customer table and invite null entries? Or, do you use a different strategy completely?
There is only one satisfying way to boot a computer.
-
I'm currently in the process of creating a service records database for storing information on machine servicing. In the un-normalised customer table I have three attributes for recording telephone contact details: LandlineNumber, MobileNumber and FaxNumber. Ordinarily, these attributes should be decomposed through the normal forms; 1NF, 2NF and 3NF. TELEPHONE_1NF(TelNumber, CustomerNumber, TelNumberType) TELEPHONE_2NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_2NF(TelNumber, TelType) TELEPHONE_3NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_3NF(TelNumber, TelType) My question is, in the real world, do you database designers out there ACTUALLY do this, or would you leave the three different telephone attributes in the customer table and invite null entries? Or, do you use a different strategy completely?
There is only one satisfying way to boot a computer.
My vote is to leave the 3 telephone attributes in the Customer Record and allow for blank/Null numbers. There is an argument for keeping things simple and in this case I would go for simplicity. :cool:
-
My vote is to leave the 3 telephone attributes in the Customer Record and allow for blank/Null numbers. There is an argument for keeping things simple and in this case I would go for simplicity. :cool:
Thanks for the advice. My college lecturer would be spitting in his beer if he even knew I was asking the question! However, having been taught the 'correct way' I'm wondering what real database designers ACTUALLY do; whether they stick to the theory or compromise.
There is only one satisfying way to boot a computer.
-
I'm currently in the process of creating a service records database for storing information on machine servicing. In the un-normalised customer table I have three attributes for recording telephone contact details: LandlineNumber, MobileNumber and FaxNumber. Ordinarily, these attributes should be decomposed through the normal forms; 1NF, 2NF and 3NF. TELEPHONE_1NF(TelNumber, CustomerNumber, TelNumberType) TELEPHONE_2NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_2NF(TelNumber, TelType) TELEPHONE_3NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_3NF(TelNumber, TelType) My question is, in the real world, do you database designers out there ACTUALLY do this, or would you leave the three different telephone attributes in the customer table and invite null entries? Or, do you use a different strategy completely?
There is only one satisfying way to boot a computer.
In most business cases I would go for the denormalised structure as David suggested. However I have seen this type on Nth level normalisation work quite well, especially if it is a commercial product and must be extensible to meet user requirements. It also requires a GREAT support team and constant retention of the IP in the development team. Corporate products can accept greater restrictions on their boundaries. If you want 4 phone number then stick it in Outlook, bugger off.
Never underestimate the power of human stupidity RAH
-
I'm currently in the process of creating a service records database for storing information on machine servicing. In the un-normalised customer table I have three attributes for recording telephone contact details: LandlineNumber, MobileNumber and FaxNumber. Ordinarily, these attributes should be decomposed through the normal forms; 1NF, 2NF and 3NF. TELEPHONE_1NF(TelNumber, CustomerNumber, TelNumberType) TELEPHONE_2NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_2NF(TelNumber, TelType) TELEPHONE_3NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_3NF(TelNumber, TelType) My question is, in the real world, do you database designers out there ACTUALLY do this, or would you leave the three different telephone attributes in the customer table and invite null entries? Or, do you use a different strategy completely?
There is only one satisfying way to boot a computer.
I also agree with what David said
-
I'm currently in the process of creating a service records database for storing information on machine servicing. In the un-normalised customer table I have three attributes for recording telephone contact details: LandlineNumber, MobileNumber and FaxNumber. Ordinarily, these attributes should be decomposed through the normal forms; 1NF, 2NF and 3NF. TELEPHONE_1NF(TelNumber, CustomerNumber, TelNumberType) TELEPHONE_2NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_2NF(TelNumber, TelType) TELEPHONE_3NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_3NF(TelNumber, TelType) My question is, in the real world, do you database designers out there ACTUALLY do this, or would you leave the three different telephone attributes in the customer table and invite null entries? Or, do you use a different strategy completely?
There is only one satisfying way to boot a computer.
I would take the view that people may have more than 3 contact numbers (attributes) and by normalising out you are allowing for any number of numbers, so to speak, and building in the ability to add as many as required in the future. On the other hand if 3 numbers is set in stone (as if anything ever really is) then leave as is.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
I'm currently in the process of creating a service records database for storing information on machine servicing. In the un-normalised customer table I have three attributes for recording telephone contact details: LandlineNumber, MobileNumber and FaxNumber. Ordinarily, these attributes should be decomposed through the normal forms; 1NF, 2NF and 3NF. TELEPHONE_1NF(TelNumber, CustomerNumber, TelNumberType) TELEPHONE_2NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_2NF(TelNumber, TelType) TELEPHONE_3NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_3NF(TelNumber, TelType) My question is, in the real world, do you database designers out there ACTUALLY do this, or would you leave the three different telephone attributes in the customer table and invite null entries? Or, do you use a different strategy completely?
There is only one satisfying way to boot a computer.
Your teacher is right, 3NF would be the minimum requirement before I'd call it a well-designed database. You are right too; in the real world, databases are created when needed, and grow over time - not all of them are designed upfront. Whenever I can, I try and normalize. Your example would make it very hard to get the complete address-details of the customer, since you'd be seeing a similar construction with ZipCodes and streets.
Digital Thunder wrote:
would you leave the three different telephone attributes in the customer table and invite null entries?
You don't want to do this;
SELECT ([phone1], [phone2], [phone3]) from ..
Before you know it, customers will want to add extra information, like a short description (e.g. "this number only between 1 and 5 am") or a bool column to flag disconnected lines. That would lead to a
SELECT ([phone1], [phone1desc], [phone2], [phone2desc], [phone3] ..
Might not sound very useful in this particular example, but I'm sure that most of us had to update a similar database at least once in their career. Imagine you have a table that all of a sudden would receive four or five more attributes :)
I are Troll :suss:
-
Your teacher is right, 3NF would be the minimum requirement before I'd call it a well-designed database. You are right too; in the real world, databases are created when needed, and grow over time - not all of them are designed upfront. Whenever I can, I try and normalize. Your example would make it very hard to get the complete address-details of the customer, since you'd be seeing a similar construction with ZipCodes and streets.
Digital Thunder wrote:
would you leave the three different telephone attributes in the customer table and invite null entries?
You don't want to do this;
SELECT ([phone1], [phone2], [phone3]) from ..
Before you know it, customers will want to add extra information, like a short description (e.g. "this number only between 1 and 5 am") or a bool column to flag disconnected lines. That would lead to a
SELECT ([phone1], [phone1desc], [phone2], [phone2desc], [phone3] ..
Might not sound very useful in this particular example, but I'm sure that most of us had to update a similar database at least once in their career. Imagine you have a table that all of a sudden would receive four or five more attributes :)
I are Troll :suss:
Eddy Vluggen wrote:
You don't want to do this; SELECT ([phone1], [phone2], [phone3]) from .. Before you know it, customers will want to add extra information, like a short description (e.g. "this number only between 1 and 5 am") or a bool column to flag disconnected lines. That would lead to a SELECT ([phone1], [phone1desc], [phone2], [phone2desc], [phone3] ..
Hmmm, I see what your saying. So, I suppose the answer to my original question is "it depends" judging by the range of replies that the question solicited. It depends on what the database is actually designed to achieve in the first place and what are the consequences moving from a theoretically correct design to a design that has compromises which fly in the face of conventional theory. My thanks go out to all of you that took the trouble to reply to the original question.
There is only one satisfying way to boot a computer.
-
I'm currently in the process of creating a service records database for storing information on machine servicing. In the un-normalised customer table I have three attributes for recording telephone contact details: LandlineNumber, MobileNumber and FaxNumber. Ordinarily, these attributes should be decomposed through the normal forms; 1NF, 2NF and 3NF. TELEPHONE_1NF(TelNumber, CustomerNumber, TelNumberType) TELEPHONE_2NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_2NF(TelNumber, TelType) TELEPHONE_3NF(TelNumber, CustomerNumber) TELEPHONE_TYPE_3NF(TelNumber, TelType) My question is, in the real world, do you database designers out there ACTUALLY do this, or would you leave the three different telephone attributes in the customer table and invite null entries? Or, do you use a different strategy completely?
There is only one satisfying way to boot a computer.
I have learned through experience to do what your instructor describes -- things will become more complex over time. Do it correctly now. "It's better to have it and not need it than to need it and not have it." "A stitch in time saves nine." At one place I worked, we needed to hold a credit card number for each account (actually, account-holders also had the option not to provide one) -- great, it (and its type; Master Card, Visa, etc.) was in the Account table. Then we had a customer who wanted to store two credit card numbers (so there would be a backup in case the other was declined :rolleyes: ) -- OK, the DBAs added it to the Account table with another field to indicate which one had been charged most recently. Then we had another customer who also wanted us to support ACH transactions against bank accounts -- oh, crap -- the DBAs added yet more fields to the Account table. I left the company after writing a document explaining that I thought that all that data should be moved to a separate table before things got completely out of hand. I suspect that in your case you want to generalize it to some sort of "contact method" table which includes telephone numbers, email addresses, icy cucumbers, etc.
-
I have learned through experience to do what your instructor describes -- things will become more complex over time. Do it correctly now. "It's better to have it and not need it than to need it and not have it." "A stitch in time saves nine." At one place I worked, we needed to hold a credit card number for each account (actually, account-holders also had the option not to provide one) -- great, it (and its type; Master Card, Visa, etc.) was in the Account table. Then we had a customer who wanted to store two credit card numbers (so there would be a backup in case the other was declined :rolleyes: ) -- OK, the DBAs added it to the Account table with another field to indicate which one had been charged most recently. Then we had another customer who also wanted us to support ACH transactions against bank accounts -- oh, crap -- the DBAs added yet more fields to the Account table. I left the company after writing a document explaining that I thought that all that data should be moved to a separate table before things got completely out of hand. I suspect that in your case you want to generalize it to some sort of "contact method" table which includes telephone numbers, email addresses, icy cucumbers, etc.
I inherited a table with over 250 columns. Many no longer used, several should have been in their own table. But the table grew just like yours (need this one thing, well now I need this one other thing). Building normalized to start is the best path.
-
Eddy Vluggen wrote:
You don't want to do this; SELECT ([phone1], [phone2], [phone3]) from .. Before you know it, customers will want to add extra information, like a short description (e.g. "this number only between 1 and 5 am") or a bool column to flag disconnected lines. That would lead to a SELECT ([phone1], [phone1desc], [phone2], [phone2desc], [phone3] ..
Hmmm, I see what your saying. So, I suppose the answer to my original question is "it depends" judging by the range of replies that the question solicited. It depends on what the database is actually designed to achieve in the first place and what are the consequences moving from a theoretically correct design to a design that has compromises which fly in the face of conventional theory. My thanks go out to all of you that took the trouble to reply to the original question.
There is only one satisfying way to boot a computer.
Digital Thunder wrote:
s moving from a theoretically correct design to a design that has compromises
Stick to the theoretically correct design, and make sure that you can explain why you denormalized that particular table. Your teach has prolly given some good arguments why you should apply at least 3NF. A good reason for denormalization would be partitioning - if the table were too large, and you'd like to divide it over two databases. IRL you'll find lots of denormalized databases, simply because there's lots of people creating databases who never heard of Codd, or decided to take a shortcut.
I are Troll :suss:
-
I have learned through experience to do what your instructor describes -- things will become more complex over time. Do it correctly now. "It's better to have it and not need it than to need it and not have it." "A stitch in time saves nine." At one place I worked, we needed to hold a credit card number for each account (actually, account-holders also had the option not to provide one) -- great, it (and its type; Master Card, Visa, etc.) was in the Account table. Then we had a customer who wanted to store two credit card numbers (so there would be a backup in case the other was declined :rolleyes: ) -- OK, the DBAs added it to the Account table with another field to indicate which one had been charged most recently. Then we had another customer who also wanted us to support ACH transactions against bank accounts -- oh, crap -- the DBAs added yet more fields to the Account table. I left the company after writing a document explaining that I thought that all that data should be moved to a separate table before things got completely out of hand. I suspect that in your case you want to generalize it to some sort of "contact method" table which includes telephone numbers, email addresses, icy cucumbers, etc.
PIEBALDconsult wrote:
I suspect that in your case you want to generalize it to some sort of "contact method" table which includes telephone numbers, email addresses, icy cucumbers, etc.
Not quite sure about the icy cucumbers :laugh: but yes, you've hit the nail on the head, that's exactly what it's for. I actually finished college 5 years ago, and this is the first occasion that has presented itself to put into practice what I actually learned, so I'm a bit rusty at the minute and having to rely on lecture notes, assignments and Database Systems (2nd Ed.) by Begg and Connolly, which is heavy going at times to say the least. :sigh:
There is only one satisfying way to boot a computer.
-
PIEBALDconsult wrote:
I suspect that in your case you want to generalize it to some sort of "contact method" table which includes telephone numbers, email addresses, icy cucumbers, etc.
Not quite sure about the icy cucumbers :laugh: but yes, you've hit the nail on the head, that's exactly what it's for. I actually finished college 5 years ago, and this is the first occasion that has presented itself to put into practice what I actually learned, so I'm a bit rusty at the minute and having to rely on lecture notes, assignments and Database Systems (2nd Ed.) by Begg and Connolly, which is heavy going at times to say the least. :sigh:
There is only one satisfying way to boot a computer.
Digital Thunder wrote:
heavy going at times
Yeah, the other week I again looked up (Wikipedia and others) third-normal-form and again couldn't make sense of the discussion. I absolutely know how to use third-normal-form, but the terms that are used to describe it are non-sensical (unless there is someone there actually telling you and applying it to some data). It's kind of like being able to read, write, and speak a language fluently but not understanding linguistics. Really, only cunning linguists need to know what the term "pluperfect" means or even that it exists. And all those hours spent diagramming sentences didn't help us speak betterishly.
-
Digital Thunder wrote:
heavy going at times
Yeah, the other week I again looked up (Wikipedia and others) third-normal-form and again couldn't make sense of the discussion. I absolutely know how to use third-normal-form, but the terms that are used to describe it are non-sensical (unless there is someone there actually telling you and applying it to some data). It's kind of like being able to read, write, and speak a language fluently but not understanding linguistics. Really, only cunning linguists need to know what the term "pluperfect" means or even that it exists. And all those hours spent diagramming sentences didn't help us speak betterishly.
PIEBALDconsult wrote:
unless there is someone there actually telling you and applying it to some data
I know EXACTLY what you mean. When the lecturer is explaining examples to drive home a point, it's quite obvious what he is actually getting at. However, I to have looked at a few examples on the web and some of them have only served to confuse me even more than I was! I did however, find a very good article that condensed a lot of detail into relatively few pages and the examples given really did a good job in explaining the detail.
There is only one satisfying way to boot a computer.
-
PIEBALDconsult wrote:
unless there is someone there actually telling you and applying it to some data
I know EXACTLY what you mean. When the lecturer is explaining examples to drive home a point, it's quite obvious what he is actually getting at. However, I to have looked at a few examples on the web and some of them have only served to confuse me even more than I was! I did however, find a very good article that condensed a lot of detail into relatively few pages and the examples given really did a good job in explaining the detail.
There is only one satisfying way to boot a computer.
Digital Thunder wrote:
confuse me even more
Exactly.
Digital Thunder wrote:
a very good article
Got a link?
-
Digital Thunder wrote:
s moving from a theoretically correct design to a design that has compromises
Stick to the theoretically correct design, and make sure that you can explain why you denormalized that particular table. Your teach has prolly given some good arguments why you should apply at least 3NF. A good reason for denormalization would be partitioning - if the table were too large, and you'd like to divide it over two databases. IRL you'll find lots of denormalized databases, simply because there's lots of people creating databases who never heard of Codd, or decided to take a shortcut.
I are Troll :suss:
Eddy Vluggen wrote:
Your teach has prolly given some good arguments why you should apply at least 3NF.
He certainly did. Basically, we were told, without normalising to 3NF we wouldn't be able to get the database to do what we want it to do and that it would be rather error prone and that if we wanted to make any money out of database design then we had better normalise to 3NF otherwise we would be penniless! The latter is a very convincing argument.
There is only one satisfying way to boot a computer.
-
Digital Thunder wrote:
confuse me even more
Exactly.
Digital Thunder wrote:
a very good article
Got a link?
Yes, there's this one I found very useful: http://www.phlonx.com/resources/nf3/[^] The one I was referring to is this one: http://www.databasedesign-resource.com/entity-relationship.html[^] and download the free ebook.
There is only one satisfying way to boot a computer.