1:1 relationship (simple...)

[dragonwell]

In my database I have two tables which have a 1:1 relationship, we will call Person and Address. If Person "has" an Address, I would think that the Address table would have a foreign key of the PersonID. BUT, I have another entity, say "Company" which also has addresses. So now Address does not necessarily belong to a Person.

So now should I have a relationship table which contains the PersonID and the AddressID, to show which address the Person has, as well as a CompanyAddress table to show which address is owned by the Company?

Also, Company will have more than one type of address (MailingAddress, PhysicalAddress, etc.)... So for each different type of address it has - a different "glue" table exists (CompanyMailingAddress, CompanyContactAddress, etc.)

Does this sound correct or is there a better way?

Greetings,
Dragonwell

 

[TheRambler]

see ganotedp post in thread669-799932

 

[willir]

Dragonwell
(as in Dragon Lance and Forgtten Relms?)

A contact database can be simple with everything to complex with many-to-many relationships.

As a suggestion, consider setting up an address table, a contact table and business or company table.


In reality, you can look at the relationship between address and company or contact as a Many-to-Many, M:M.

A street address can have many contacts - such as menbers in a family. Likewise, a person may have more than one address (although they will have a main address). Example, a summer and winter address for the "snowbirds". Ditto for a student with their university address and home address.

The same for businesses. A company can have several addresses throughout a country, or even in different countries. Likewise, an address may have several businesses (mail order businesses for example).

If you accept this argument, then you can setup to joiner or intermediary tables - one for the people and one for buseiness.

CompanyAddressTbl
CompanyCode - foreign key to Company table
AddressID - foreign key to Address table
AddressType - allow brief description to define the type of address
PrimaryAddress - Yes / No field, required for mailing

Use a similar approach for the ContactAddress joiner table.

This type of design takes a bit more work in the layout, but adds much for flexibility.

Richard

 

[dragonwell]

Thanks Richard,

I'll probably do something like that, except that in this system, there are only three distinct Address types - two for company, one for people (this is not really a "contacts" database). So, I am going to have three joiner tables - PersonAddress, CompanyPublicAddress, CompanyPrivateAddress, each with just an ID field(PK), a FK addressID, and an FK of of either the personID or companyID.






Greetings,
Dragonwell

 

[CajunCenturion]

Could you add an AddressType field to the Address table?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[willir]

Good one Cajun.

I actually use a AddressType field in the aforementioned example in the joining table; more geared to... "shipping", "billing", "general", but using "public" and "private" would work too.

Using AddressType on the address table should work.