Data Normalization R Tanner <tanner.robin[ at ]gmail.com> 10/16/2008 9:50:20 PM I understand what the purpose and value of data normalization is, but I just have one thing I am confused about. If you split up a table, then it will create two tables. The child table will have a lookup column to the parent table, which will have whatever value is applicable that relates that given record to it's parent. What I don't understand is how that is more efficient. In fact it seems to duplicate the data if anything...

Re: Data Normalization "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 10/16/2008 10:58:54 PM The efficiency comes in that normally the parent record has multiple fields associated with it, so you don't have to repeat that data in each of the child records. Think of an invoice. There's information that's common to the entire invoice: the invoice number, the invoice date, the name of the customer, the address of the customer and so on. Then there are the individual invoice lines: the product, the quantity and the price. You wouldn't want to have the customer's address repeated on each of the invoice lines. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "R Tanner" <tanner.robin[ at ]gmail.com> wrote in message news:8fca1742-8266-4932-bdc7-78b857dca96f[ at ]40g2000prx.googlegroups.com... [Quoted Text] >I understand what the purpose and value of data normalization is, but > I just have one thing I am confused about. If you split up a table, > then it will create two tables. The child table will have a lookup > column to the parent table, which will have whatever value is > applicable that relates that given record to it's parent. What I > don't understand is how that is more efficient. In fact it seems to > duplicate the data if anything...

Re: Data Normalization R Tanner <tanner.robin[ at ]gmail.com> 10/17/2008 12:25:32 AM On Oct 16, 4:58 pm, "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote: [Quoted Text] > The efficiency comes in that normally the parent record has multiple fields > associated with it, so you don't have to repeat that data in each of the > child records. > > Think of an invoice. There's information that's common to the entire > invoice: the invoice number, the invoice date, the name of the customer, the > address of the customer and so on. Then there are the individual invoice > lines: the product, the quantity and the price. You wouldn't want to have > the customer's address repeated on each of the invoice lines. > > -- > Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele > (no private e-mails, please) > > "R Tanner" <tanner.ro...[ at ]gmail.com> wrote in message > > news:8fca1742-8266-4932-bdc7-78b857dca96f[ at ]40g2000prx.googlegroups.com... > > >I understand what the purpose and value of data normalization is, but > > I just have one thing I am confused about.  If you split up a table, > > then it will create two tables.  The child table will have a lookup > > column to the parent table, which will have whatever value is > > applicable that relates that given record to it's parent.  What I > > don't understand is how that is more efficient. In fact it seems to > > duplicate the data if anything... Okay I can understand that. So, let me pose a scenario to you. Say you have 5 different invoices. In an unnormalized table, on each line, you would see the product, quantity, price, customer address, customer name, and invoice number. Since there are 5 different invoices, you would have 5 different records here in one table. 3 with the same customer, 2 with a different customer. To normalize the table, you would want to split the table to create a customer table with the customer name and address. The invoice number would be the primary key in the invoices table and then a foreign key in the customers table. If I wanted to have a form update my database with a new invoice, I would link the form to the invoices table and have the form add the details, with a field on that form that allows for the entry of the name of the customer. Is that correct? I think I am kind of starting to understand relational databases. For data integrity, there is no comparison. That probably goes without saying however. :)

Re: Data Normalization R Tanner <tanner.robin[ at ]gmail.com> 10/17/2008 12:26:51 AM On Oct 16, 6:25 pm, R Tanner <tanner.ro...[ at ]gmail.com> wrote: [Quoted Text] > On Oct 16, 4:58 pm, "Douglas J. Steele" > > > > <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote: > > The efficiency comes in that normally the parent record has multiple fields > > associated with it, so you don't have to repeat that data in each of the > > child records. > > > Think of an invoice. There's information that's common to the entire > > invoice: the invoice number, the invoice date, the name of the customer, the > > address of the customer and so on. Then there are the individual invoice > > lines: the product, the quantity and the price. You wouldn't want to have > > the customer's address repeated on each of the invoice lines. > > > -- > > Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele > > (no private e-mails, please) > > > "R Tanner" <tanner.ro...[ at ]gmail.com> wrote in message > > >news:8fca1742-8266-4932-bdc7-78b857dca96f[ at ]40g2000prx.googlegroups.com... > > > >I understand what the purpose and value of data normalization is, but > > > I just have one thing I am confused about.  If you split up a table, > > > then it will create two tables.  The child table will have a lookup > > > column to the parent table, which will have whatever value is > > > applicable that relates that given record to it's parent.  What I > > > don't understand is how that is more efficient. In fact it seems to > > > duplicate the data if anything... > > Okay I can understand that.  So, let me pose a scenario to you.  Say > you have 5 different invoices.  In an unnormalized table, on each > line, you would see the product, quantity, price, customer address, > customer name, and invoice number.  Since there are 5 different > invoices, you would have 5 different records here in one table.  3 > with the same customer, 2 with a different customer. > > To normalize the table, you would want to split the table to create a > customer table with the customer name and address.  The invoice number > would be the primary key in the invoices table and then a foreign key > in the customers table. > > If I wanted to have a form update my database with a new invoice, I > would link the form to the invoices table and have the form add the > details, with a field on that form that allows for the entry of the > name of the customer.  Is that correct? > > I think I am kind of starting to understand relational databases.  For > data integrity, there is no comparison.  That probably goes without > saying however. :) I think I have the only post in this forum on anything to do with Access. Strange.

Re: Data Normalization "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 10/17/2008 9:16:20 PM Actually, since one customer may have many invoices, you'd have the customer number as a foreign key on the invoice table, not vice versa. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "R Tanner" <tanner.robin[ at ]gmail.com> wrote in message news:fc17914e-a68a-4cc7-8642-c195e7cd204d[ at ]q26g2000prq.googlegroups.com... On Oct 16, 4:58 pm, "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote: [Quoted Text] > The efficiency comes in that normally the parent record has multiple > fields > associated with it, so you don't have to repeat that data in each of the > child records. > > Think of an invoice. There's information that's common to the entire > invoice: the invoice number, the invoice date, the name of the customer, > the > address of the customer and so on. Then there are the individual invoice > lines: the product, the quantity and the price. You wouldn't want to have > the customer's address repeated on each of the invoice lines. Okay I can understand that. So, let me pose a scenario to you. Say you have 5 different invoices. In an unnormalized table, on each line, you would see the product, quantity, price, customer address, customer name, and invoice number. Since there are 5 different invoices, you would have 5 different records here in one table. 3 with the same customer, 2 with a different customer. To normalize the table, you would want to split the table to create a customer table with the customer name and address. The invoice number would be the primary key in the invoices table and then a foreign key in the customers table. If I wanted to have a form update my database with a new invoice, I would link the form to the invoices table and have the form add the details, with a field on that form that allows for the entry of the name of the customer. Is that correct? I think I am kind of starting to understand relational databases. For data integrity, there is no comparison. That probably goes without saying however. :)