Complex Table relationship question.

[vree]

For anyone else out there, who may not have a weekend life.
I am trying to redesign a db, and improve the design, and am at a impasse. Need fresh ideas.

I have table Companies, a table of all available Companies
tblOrg
lngOrgId
OrgName

I have Addresses, a table of all available addresses.
tblAddesses
lngAddressID
StreetAddress
POBoxAddress
City
State
Zip

I have table People, a table of all avaiable people.
tblPeople
lngPeopleId
FirstName
LastName
Title
Suffix

Now here is where I am running into problems getting it straight and maintaing good design.
Companies can have more than one Address of different Address type.
thus
tblOrgAddress
lngOrgAddressID
lngOrgID
lngAddressID
AddressType

Works beautiful
People however, may work for one or more companies, or no company, AND may also have a personal address.

Right now i have:
tblOrgContacts
lngOrgId
lngPeopleID
lngAddressID
Phone
Fax
Email
Pager


But I need something like, a person may or may not have a company, and may or may not have EITHER a company address or a personal address.

There is more but this is the basic layout.



vree

http://www.groundworkssoftware.com

"For a succesful technology, reality must take precedence over public
relations, for nature will not be fooled."
R.Feynman

 

[Golom]

If the relationship between company and address is "zero or one to many" (i.e. a company may have zero or more addresses) then you need a "CompanyAddress" table like

TABLE COMPADDR
lngOrgId (PK)
lngAddressID (PK)

The relationship between Company and Person is "one to many" (i.e. a company may have many people but a person has at most only one company.)

TABLE COMPPERSON
lngPeopleID (PK)
lngOrdID

For the relationship between People and Addresses, you have a choice since the relationship is "zero or one to one" (i.e. a person may not have an address but, if he does have an address, he has only one.) You could make the AddressID a field in the Person record since that it will be an attribute of the key, the whole key and nothing but the key (that is, it is in third normal form). If there is a possibility that a person can have more than one address (a one to many relationship) then a separate table like

TABLE PERSONADDR
lngPeopleID (PK)
lngAddressID (PK)

is required. A table like this also allows you to ask questions like "Who are the people that live at the same address? (If such questions have relevance in your application.)

With your existing tables, you need to clear up some confusion (at least my confusion).

tblOrgAddress
lngOrgAddressID
lngOrgID
lngAddressID
AddressType <-- Is &quot;AddressType&quot; an attribute of the address?
If so should it be in the Address table?

tblOrgContacts
lngOrgId
lngPeopleID
lngAddressID
Phone <--
Fax <-- Are these fields attributes of &quot;People&quot;
Email <-- Should they be in the &quot;People&quot; table?
Pager <--

With this structure a Person must have a company and an address in order to have Phone, Fax, etc. information. You will be forced to add NULL company and/or address records to this table just to record that information. This structure, because it has OrdID, PeopleID and AddressID, requires that a person be associated with a company in order to link the person to an address.

 

[vree]

If the relationship between company and address is &quot;zero or one to many&quot; (i.e. a company may have zero or more addresses) then you need a &quot;CompanyAddress&quot; table like

TABLE COMPADDR
lngOrgId (PK)
lngAddressID (PK)
I have this in OrgAddress table.
The relationship between Company and Person is &quot;one to many&quot; (i.e. a company may have many people but a person has at most only one company.)
This statement is incorrect. In the line of work I am in, people work for more than one company.

tblOrgAddress
lngOrgAddressID
lngOrgID
lngAddressID
AddressType <-- Is &quot;AddressType&quot; an attribute of the address?
If so should it be in the Address table?
This is an organization address type. Organizations, can have more than one type of address. Billing, Shipping, Central Office, Corporate.
tblOrgContacts
lngOrgId
lngPeopleID
lngAddressID
Phone <--
Fax <-- Are these fields attributes of &quot;People&quot;
Email <-- Should they be in the &quot;People&quot; table?
Pager <--
When I started, I was thinking, this would be a record of the person by company. But really I think, it is person by address type, because people may have multiple address types ie Organization address type, or personal address type.

Hope this clears it up a bit.

I have this
Company---->Address One to Many
People ---->Address One to Many ie(personal and business)
Company <--->People Many to Many




vree

http://www.groundworkssoftware.com

&quot;For a succesful technology, reality must take precedence over public
relations, for nature will not be fooled.&quot;
R.Feynman