[cpuguru07]

I have several tables such as:

Offices
----------
GUID (uniqueidentifier, PK)
[other fields]

Users
---------
GUID (uniqueidentifier, PK)
[other fields]

Clients
---------
GUID (uniqueidentifier, PK)
[other fields]

Vendors
---------
GUID (uniqueidentifier, PK)
[other fields]

[more tables]

and I want these tables to be able to have multiple addresses (home, work, billing, shipping, physical, PO box, etc.), so I have the following Addresses table:

Addresses
---------
GUID (uniqueidentifier, PK)
ParentGUID (uniqueidentifier, FK)
Street
City
State
Zip
[etc]

How do I make the ParentGUID of the Addresses table a FK that relates to all of the other tables? Or is there a better approach?

[samuraitux]

Not sure I totally understand your question but I am going to attempt this. What I would do is have Three tables.
The first would be the customers table, Then I would have a CustomerAddressess table. Then I would have an AddressType table. So your Customers table would be like ID, FirstName, LastName, email. etc. The CustomerAddresses table would have CustomerID, AddressTypeID, City, State, Zip, (course other columns for say International Users.) The AddressType table would be ID, Name.

This way you would reduce your number of tables but still be able to give them Different Types. Now in the CustomerAddresses Table I did not make an ID key because you I was using the CustomerID and AddressTypeID to make the Primary Key. However if want to allow the user to have more than one home address then I would use a synthetic key like ID instead.

[baavgai]

A foreign key relates to the primary key of a single table. Period.

If you want a more esoteric integrity constraint, you'll have to write a trigger. The trigger would then look in all those tables you want to filter by and check for the key. Of course, this doesn't much help if you want to find out what element an address belongs to, because it has multiple options.

You'd be better off doing:

Office
----------
OfficeGUID (uniqueidentifier, PK)
AddressGUID (uniqueidentifier, FK)

User
---------
UserGUID (uniqueidentifier, PK)
AddressGUID (uniqueidentifier, FK)

If you need multiples, you're kind of stuck with another table.

Either:

OfficeAddress
----------
OfficeGUID (uniqueidentifier, PK, FK)
AddressGUID (uniqueidentifier, PK, FK)

UserAddress
---------
UserGUID (uniqueidentifier, PK, FK)
AddressGUID (uniqueidentifier, PK, FK)

Or:

OfficeAddress
----------
OfficeAddressGUID (uniqueidentifier, PK)
OfficeGUID (uniqueidentifier, FK)
Street
City
State
Zip

[cpuguru07]

So are you saying I'd have a different set of tables for each table in my above example? (e.g., Clients, ClientAddressses; Offices, OfficeAddresses; Users, User Addresses; Vendors, VendorAddresses, etc? Would I have a single AddressType table, or a corresponding AddressType table for each of these sets of tables?

To clarify what I'm trying to do, let's use the example of Notes instead of Addresses (it's the same thing I'm trying to do with both of these):

I have many tables (Orders, Invoices, Clients, Vendors, Users, etc.), where each of these can have many associated notes. (Say one of our clients needs more time to pay their next invoice, and we want to make a note of this, so on that particular Invoice, a user may add a note that says "Client requested extension until [date]." Then, when the invoice comes past due and another employee goes to call the client to remind them to pay, that note will show up and they know they requested an extension, then they can follow up with the client and add additional notes as necessary.)

Instead of having OrderNotes, InvoiceNotes, etc., I'd like to just have one Notes table, where each row in the Notes table has a unique ID (either a GUID or an Identity), a ParentGUID (representing the primary key of the note's parent item [could be an Order, an Invoice, a Client, etc]), a Text field for the note itself, the ID of the user who added the note, and the datetime the note was created. How do I make a ForeignKey constraint on ParentGUID that references all of Orders.GUID, Invoices.GUID, Clients.GUID, etc. The challenge is that a traditional ForeignKey constraint requires the key to exist in ALL of the tables it is related to (which means there would need to be a corresponding Order, Invoice, Client, Vendor, etc. for EVERY note). I just want to have a single Note correspond to a single [Order, Invoice, Client, Vendor, etc.].

Hopefully this helps clarify what I'm trying to do - I hope I'm not being too confusing!

[samuraitux]

A foreign key can only link back to one table. It can't link to multiple tables. So for say your notes you will need to have either a Notes table that will correspond to your Invoices or Orders Table for have a column for each invoice and orders called notes.

[cpuguru07]

Sorry, I didn't get baavgai's answer before I replied!

(Using the Notes example because I think it's simpler - I don't know why I didn't use that in the first place, haha.)

Since I don't want to have multiple Notes tables (OrderNotes, ClientNotes, VendorNotes, etc.), an alternative approach would be to get rid of the ParentGUID column in the Notes table and add OrderGUID, ClientGUID, VendorGUID, etc. and relate each of those columns to their appropriate tables. Would this be a viable option?

[samuraitux]

It sound like it would work. But from a business stand point, When a vendor gives you a specific note, is it usally in relation to an order that you placed with them, if so then why not create a notes field since it is a part of the order. Same goes for a customer, what do their notes normally have in relation to your database.

I normally try and design my databases around the business. So if I am selling a product (Just a simple example.) I might have,
customers, Orders, OrderDetails, Products, Vendors, VendorProducts (Multiple vendors might sell the same product). Now if a customer needs a special note with an order I have it a column in the orders table because that order and note are a part of the same SET/(Relation).

[cpuguru07]

Right, a system should be built around the business - this business wants to have multiple notes for each item in order to record conversation histories and who made a note and when they made it.

Basically, this is for all of the miscellaneous information a client (for example) may give us that other users may need to know. If a client calls and says they have received too many late orders, an employee can record that in the system, and then when our client relations person goes through the clients, she can see that note and follow up with them several weeks later to see if the issue is being resolved, make appropriate notes, and maybe follow up again in a few months.

The way they want it to work is such that this (the multiple notes system) applies to almost anything in the system (a particular Invoice, a Client in general, a line item on an order, etc.).

Additionally, I would like to carry this principle (multiple relationships) over to the Addresses table so that Clients, Vendors, etc. can have multiple addresses - so it's not always as simple as a Notes field.