> On Wed, 16 Aug 2006 23:40:29 -0400, Jerry Stuckle
> <jstucklexattglobal.net> wrote:
>>This is not an easy question. Is this a business address? If so, then
>>the address should be related to your Company table (a company could
>>have multiple addresses). However, if this is an individual's address,
>>then it should be related to the Contact table.
> Jerry - your original response kicked me into life, made me recall
> important philosophical axioms from uni days like "An organisation is
> an abstract entitiy - its really a collection of people" and also made
> me realize how everyday items may have to be viewed very differently
> when placed in the context of relational database.
>>The other problem you run into is - Contact and Company may have
>>duplicate primary keys (i.e. you may have a PK of "3" in both tables).
>>Which one would the address refer to?
>>Normalization is not an exact science. But generally, when you have a
>>subservient table such as Addresses, it's not good to have two master
>>tables referring to it.
> Mindreader - this is exactly the problem I'm now grappling with (once
> I accepted that there is a damn good reason for so many tables in a
> database - where I once thought a couple of tables would suffice).
> My mental block (before reading posts from you guys) was I sort of
> instinctively felt that a 'master' names table, with a 'type' field
> was the way forward. Thus, whether the query was on a Company,
> Individual, Supplier, Bank, Employee, even Favourite Restaurant, etc -
> only one 'names' table would be consulted for email, tel, password,
> How naive, now I understand how many issues there are involved.
> I do try hard to research before ever posting, but some things get us
> newbies every time - so I have an 'inexact science' type question.
> I understand there can be a threat to autonumber (in some database
> manipulations). The proposed solution seems to usually involves using
> a separate table to hold PK/s and incrementing. Us newbies feel the
> threat must still be very real, if the new PK's values are still in a
> table within the db, ie; still subject to the databases integrity.
> Which way (on balance) do you feel should one err -
> (1) ALL self-generated PK's
> (2) ALL Autonumber PK's
> (3) Mixture of both, based on preservation criticality.
> Many thanks