1:1 relationship in database (simple...) 2

[dragonwell]

(if there is a "Database-Design/Modeling" forurm, please excuse this, but this seems like the best place)

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

 

[williamu]

I've been reading this thread with interest and as a result I thought I'd let you know how we do this.

Given that you have a base table used to store address information with a construction such as:

Unique_ID
Addr_Type
Addr_1
Addr_2
Addr_3
Town
Postcode

The idea here is that the Addr_Type field is a long data type that is effectively a bitmask of the type of address it represents.

Address_Type
Home = 1
Company = 2
OtherAddr = 4
Another = 8

When a record is created the same address may well be applicable to one or more instances. It would then be a simple matter of ANDing the two (or more) values of the Address_Type together.

When a search is required you can the use this mask to search for any number of addresses:

SELECT * FROM Company WHERE (Addr_Type AND MyMask = MyMask) this would allow you to search for either a home address or company address simply by ANDing the Addr_Type with a Mask of 3.

Yes, this approach does have its limitations, in that you are restricted to 31 or 63 (depending on architecture) types of address as you would need to use FFFFFFFF to search for ANY address type.

In addition to the ease of use you also have the advantage that the speed of searches is greatly increased due to the number crunching approach and the removal of string comparisons.

Well it's food for thought.




William
Software Engineer
ICQ No. 56047340