Re: Data Normalization

In Response to your question on Invoices and customers.
You came pretty close, except the INVOICES table would contain a CustomerID foreign key. The Customer table would not have an invoice id.

One Customer has (or could have) many Invoices. The many side gets the Foreign key.


In response to your question on Primary keys
The Primary key should meet the following constraints:
A) It must be UNIQUE. No duplications
B) It should be stable (that is it should not change over time). There are ways to handle changes in the value but if possible (in my opinion) they should be avoided.
C) It must be available for every record.
Social Security Numbers (SSN) for people in the US are problematic. New Borns don't have them immediately, visitors from overseas don't have them, sometimes people won't share them, etc. It depends on the purpose of the database whether an SSN would be a good candidate for a primary key.

Names of people are not unique and are not stable (marriage, divorce, etc). Names of the States in the United States are stable and probably will not change. Abbreviations for names of the States are stable and do not change frequently (To Date: once in my lifetime).

So there is nothing wrong with using a Natural Key (some item or items of data in each record) or with using an artificial key (autonumber or other unique value that has no relation to the contents of the record). This subject Natural vs Artificial can become a holy war with some individuals. I use both depending on the table and situation.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


R Tanner wrote:
On Oct 16, 4:57 pm, John W. Vinson
<jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
On Thu, 16 Oct 2008 14:51:30 -0700 (PDT), R Tanner <tanner.ro...@xxxxxxxxx>
wrote:

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...
It duplicates *one field*. Your related table may have many fields!

Just for example, consider a People table with a PersonID, related one to many
to an Address table. Each person may have several addresses; each record of
the address table would have fields like AddressNo, Direction, Street, Suffix
(i.e. St., Ave., Blvd.), Postcode, City, State and Country. These fields don't
need to occur in the parent table and need not be duplicated.
--

John W. Vinson [MVP]

Also in reply to your post about the primary and foreign keys - -
When I use the table analyzer and I split a table, the lookup column
to the parent table will have the actual values that are in the parent
table. Should this be the case, or would it not take up less memory
in the database to have the lookup column look up the primary key in
the parent table, which would be an autonumber...
.

Relevant Pages

  • Re: Data Normalization
    ... applicable that relates that given record to it's parent. ... duplicate the data if anything... ... Say you have 5 different invoices. ... customer name, and invoice number. ...
    (microsoft.public.access.queries)
  • Re: MS Access
    ... primary key has some blank, ... We can call this our parent or so called "main" table. ... you must declare a new column in this child table. ... In the above you can tell the customer with an id of 2 has a favorite color ...
    (microsoft.public.access.gettingstarted)
  • Re: Inheriting Primary Key Names
    ... parent It makes it ... > Let's assume you have 3 entities, Person, Member, and Customer. ... a sub-class of Person; Customer is a sub-class of Member. ... primary key for Person is personId. ...
    (microsoft.public.sqlserver.programming)
  • Re: AutoLookup Query subform
    ... Select the subform control. ... Next, how do you select a particular "parent" (i.e., Customer) in the main ... error message " INDEX OR PRIMARY KEY CAN NOT CONTAIN A NULL VALUE". ...
    (microsoft.public.access.forms)
  • Re: AutoNumber Question
    ... the actual 5 number account as the primary key in the customer table. ... You have a One to Many (invoices, bills) ...
    (microsoft.public.access.tablesdbdesign)