Re: Data Normalization

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: Deleting duplicate parents and grouping children to single par
    ... You will need to build the action query as a SQL statement to execute. ... To reassign 24 to 18, and then kill 24, this is the basic code: ... duplicate fields and then use a MIN function to identify the first ParentID ... undesirable parent record. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Deleting duplicate parents and grouping children to single par
    ... first the update query and then the delete query. ... duplicate fields and then use a MIN function to identify the first ParentID ... undesirable parent record. ... the process whereby, after import, duplicate parent records are identified ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Notify User of Duplicate Records on Multiple Fields
    ... These 4 text boxes are the only available fields on a parent form. ... form has 50+ fields and ultimately hits a child table by way of a query. ... Then, if there is a duplicate, the parent form ...
    (microsoft.public.access.macros)
  • RE: Help Change existing Code
    ... but it is returning the values when the parent ID is ... 'First Sort Data ... Start = RowCount ... If Duplicate = False Then ...
    (microsoft.public.excel.programming)
  • Re: Deleting duplicate parents and grouping children to single parent
    ... you need a way to identify "duplicate" parents. ... Essentially you will execute an Update query to change the ParentID of the affected children, and then exeute a Delete query to kill off the undesirable parent record. ... the process whereby, after import, duplicate parent records are identified ...
    (microsoft.public.access.tablesdbdesign)