Many to many with three tables 2

[glgcag1991]

I have a Clients table and a Vendors table, both of which I need to connect to an Addresses table through an associate table for a many to many relationship. So, rather than having to create two associate tables (t_Clients_Addresses & t_Vendors_Addresses) I would like to create t_Contacts_Addresses to link both Clients and Vendors tables to Addresses table- the associate table fields would look as follows:

pk_Contacts_AddressID
fk_Clients_ClientID
fk_Vendors_VendorID

If the address corresponds to a vendor, the vendor fk will exist, if it corresponds to a client, the client fk will exist. This way, if a client and vendor share the same address, I can reuse it.

I've always created separate associate tables for many to many relationships, but in this case, it seems to work having one table to handle both Clients and Vendors. Is this efficient and a good way to handle it, or should I do it differently? Thanks for the help and suggestions!

 

[gmmastros]

I wouldn't do it this way.

The problem with this approach is that it does not lend itself to future feature enhancements. For example, suppose you have a client (or vendor) with multiple locations. How would you handle that? Or.... with multiple locations, suppose you wanted to add a column for description (Corporate Office, Miami Office, London Office, Shipping Address). If you have separate tables, it will be easier (later) to add this functionality without causing you a lot of work.

I would encourage you to have separate tables, one for client addresses and another for vendor addresses.

Please understand that this is just my opinion (for whatever that's worth).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glgcag1991]

I do have separate tables- I forgot a fk in the original question. The Addresses table contains the address info. The associate table t_Contact_Addresses is simply the many to many relationship table that connects the Clients and Vendors to their addresses. I can have as many addresses for a Client or Vendor as needed this way.

Here's the relationship:

t_Clients
\
t_Contact_Addresses - t_Addresses
/
t_Vendors

t_Contact_Addresses contains ClientID fk and VendorID fk as well as AddressID fk (which I forgot to include in my original question- sorry!)

 

[markros]

I haven't read gmmastros suggestion yet, but I would do it differently.

I would have Address Table. Vendors Table and Clients table. And I would have

VendorAddresses
VendorID AddressID

and ClientAddresses
ClientID AddressID.

Sounds like better design to me.

In our database we used Address, Table_Name, Fk structure, but it was quite limited. Unfortunately we have no luxury to change the design, so we had to go with certain problems and limitations.

 

[glgcag1991]

Markros,

Thanks for the suggestion, but I want to reuse the addresses on occasion as, believe it or not, some of our clients have the same addresses as our vendors. I suppose I should just create two associate tables and connect that way:

t_Clients -- t_Clients_Addresses -- t_Addresses

and

t_Vendors -- t_Vendors_Addresses -- t_Addresses

This provides the same functionality of a Client or Vendor being able to have unlimited addresses associated with them, and it still has all addresses contained in one table, I was just trying to shortcut even this architecture by putting both associate tables (t_Client_Addresses & t_Vendor_Addresses) into one (t_Contact_Addresses). You guys have definitely raised enough flags that I think I probably shouldn't do it that way . . .

 

[markros]

Yes, that was my exact suggestion. The way you structured it now will allow to reuse the same address by different vendors and clients and have both vendor and client point to the same address too. So, your latest scheme is what I would implement if I would design this database.

 

[RiverGuy]

You should not have just one Address table. If you've got two different departments - one which maintains Vendor information, and one which maintains Client information - but both of those departments or people are updating a common address, it's not guaranteed to work.

If you need to update the address for the Client, you'll have to create a new Address record and update the ClientAddress table to point to the new Address record. Otherwise, you would update the Vendor's address as well. And maybe the Vendor didn't really change addresses like the Client did. Now it's not a big deal to code a stored procedure to do the insert/update for you, but why add more complexity when you don't have to?

 

[markros]

Hmm, this is also a valid point.

 

[glgcag1991]

RiverGuy,

Good point! I hadn't thought of that- I'm always thinking of ways to cut down on duplication but in this case, duplication is necessary.

It's a weird situation because since I work in entertainment, many of the production companies and small producers have the same address as the major studios since they are housed on the lot. I DO want to be able to lookup and use the studio address on multiple clients/vendors, but instead of linking to that address, I can just have it copy to the new address record.

The users want to be able to search for the studio address and insert it in the client's address fields without having to re-type it. Would you still separate out the Clients and Vendors addresses and just have a lookup of addresses run across both tables or do you think it's OK to store them all in one table? (We're talking about address records in the 50k range and will probably only double in the next 5 years.)

 

[RiverGuy]

Now that I think about this more, I think you're thinking about this too much. You're trying to make an entity out of an address. But to me, address is really just an attribute of a client or vendor. Would it really matter you end up with 123 Main St #26 listed five times? It's really not that much different than having John Smith listed five times. And I know you're not thinking about having a last names table to consolidate all of your different clients named John Smith.

I think your user interface hints are a completely separate issue. If your application developer (maybe that's you too) wants to save the users some time by suggesting existing addresses, you shouldn't need to design the database to simply suit that need.

Another thing to consider is that it is common for companies to use data from the postal service or another third party to cleanse and standardize addresses. I would definitely suggest looking into that, or you might eventually end up with stuff like "123 Main Street", "123 Main St", "123 Main St." in your records. And no your single table design won't prevent that because what's to stop the user from bypassing your suggestion and typing it incorrectly. Also, if you want to suggest an existing address to the data entry user, why not suggest a properly formatted address?

If I was designing the database, I would lean towards the following:

Clients
--------
ClientID PK
ClientName
etc.

ClientAddress
-------------
ClientID FK
AddressLine1
AddressLine2
etc.
PreferredAddress

Vendors
---------
VendorID PK
VendorName
etc.

VendorAddress
--------------
VendorID FK
AddressLine1
AddressLine2
etc.
PreferredAddress

And yes, you may end with additional columns which are specific to one type of address or the other as George mentioned.

 

[glgcag1991]

RiverGuy,

Thank you for the clear explanation. I am the "end to end" designer and am feeling the pain since I'm coming from Access into SQL Server 2005 and ASP.NET completely blind. I will definitely implement your suggestion to save myself a headache.

Thanks for the suggestion on the postal service stuff- I have purchased zip code info in the past, but I'll look around for the suggested formatting of addresses and implement it in the logic.