Re: A Normalization Question

>Subject: Re: A Normalization Question
>From: "Alan" not.me_at_uhuh.rcn.com
>Date: 2004-06-28 8:52 AM Eastern Daylight Time
>Message-id: <swUDc.10129$Av3.4182_at_nwrdny01.gnilink.net>
>
>"VHarris001" <vharris001_at_aol.com> wrote in message
>news:20040626120209.29625.00000578_at_mb-m16.aol.com...
>> Over the years, I've tried several different relational database schemes
>for
>> tracking personal and professional information, and in every instance have
>been
>> frustrated with the results.
>>
>> The biggest problem seems to lie in identifying entities, or 'things.'
>For
>> instance, when I set up a corporation as a business entity in a table, and
>use
>> other tables for employees, vendors, customers, strategic partners, etc.,
>I
>> find that some employees are also vendors, customers, and partners. Of
>course,
>> this means that I must enter the same person (or 'thing') into multiple
>tables.
>>
(snip)
>>
>> V Harris
>
>Your normalization problem is really a business definitions problem. If you
>were to create an ERD first, you would have no problems normalizing. This is
>a standard employee-supervisor problem, where an employee has a supervisor
>and a supervisor is, of course, an employee. It is a recursive relationship.
>You need one table with multiple FKs (one for each relationship) that refer
>to itself.
>
>For example, we'll use Tax ID Number (TIN) as the PK. In the U.S., all TINs
>are nine digits ssn and company tin and do not overlap. So your table may
>look like this:
>THINGS
>thing_tin (pk),
>name,
>address,
>etc
>vendor_tin (fk),
>emp_tin (fk),
>supplier_tin( fk),
>customer_tin (fk),
>partner_tin (fk),
>etc
>
>You then join the table to itself, using the proper FK. If (when!) you have
>multiple addresses (or other attributes), you then split out all of the
>non-fk attributes in the THINGS table, leaving behind a many-to-many linking
>table:
>
>THINGS
>thing_tin (pk),
>vendor_tin (fk),
>emp_tin (fk),
>supplier_tin( fk),
>customer_tin (fk),
>partner_tin (fk),
>etc (fk)
>
>
>VENDOR_ATTRIBUTES
>vendor_tin (pk/fk),
>name,
>address,
>etc
>
>EMPLOYEE ATTRIBUTES
>emp_tin (pk/fk),
>name,
>address,
>etc
>
>
>ETC_ATTRIBUTES
>.
>.
>.
>
>That should do it.

I should have made more clear that I didn't intend for this to be just a business database, but rather a comprehensive database for both business and personal use.

So, can the example you cite above accomodate both the business and personal relationships of, say, a closely held (family) corporation?

That is, it would track regular employees, but could it also track some of the employees as husband and wife, children, parents, grandparents and other assorted relatives, who are both employees and non-employees?

For instance, a business database should not include certain personal information about an employee, while a private database should include personal information about relatives. If a relative is also an employee, must there be another table to contain the personal data?

We probably wouldn't want to have in the above table these fields:

THINGS
married_date (fk),
separation_date (fk),
divorce_date (fk),
asset_division (fk),
custody_arrangement (fk),
support_payment (fk)
etc.

These fields are,of course, unnecessary for most THINGS, but are very necessary for some THINGS.

To normalize the data, must I crreate another table MARITAL HISTORY?

And how about tracking the extended family? Can we show all these relationships and attributes on the ERD?

V Harris Received on Fri Jul 02 2004 - 16:11:31 CEST