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.

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?

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!