Sharing one unique ID series among several tables

[SJM]

I have two tables, Client and Vendor. They can each have an indefinite number of addresses. Therefore I decide to make an Address table. In this table, each record is an address (tuple of street, city, zip, type ("Shipping", "Billing", etc.), etc.). One of the columns is a foreign key, whose value is the primary key of the entity whose address this is.

Now the question: How do I have the primary key field of the two tables Client and Vendor share the same space of unique serial numbers, so that no key is used in both tables? Otherwise I have to make two address tables, ClientAddress and VendorAddress, which is annoying for a variety of reasons.

More generally, how can I have n tables share a single series of UUIDs? (Universally Unique Identifiers) Is there a best practice? Can it accomodate adding another table to the mix later on?

Alternatively, is my approach misguided and I should in fact separate the address tables for the two entities?

[Ender]

The Address table can have an auto-entered serial number for its own primary ID. It will also have a foreign key for each of the Client and Vendor relationships. This foriegn key could be two separate fields, or one field if the relationships have an additional filter to identify the parent:

Client <=> Address by Client =

Client::CientID = Address by Client::ParentID

AND Client::gClient = Address by Client::ParentType

Vendor <=> Address by Vendor =

Vendor::VendorID = Address by Vendor::ParentID

AND Vendor::gVendor = Address by Vendor::ParentType

Populate gClient in Client with some text like "Client", and in Vendor, populate gVendor with "Vendor". These should be text fields with global storage.

[Kent Searight]

If it were my choice, I'd (probably) not divide clients and vendors into 2 tables. What do you do when one of your vendors is also your client? I encounter this scenario frequently.

Instead I'd create a single "Contacts" table in which I'd have a way of classifying whether a contact is a vendor or a client (or a sales prospect, etc.).

You could then create Table Occurrences with 2 keys (contactID & contactTypeID) as needed to view the addresses as they relate to Vendors or whatever.

I hope this helps...maybe someone else could put in their 2 cents, as well. :qwery:

[SJM]

I feel like your proposal is slightly more complicated than necessary. I see now that, for each Table whose rows can have an associated set of Address records, I just add a foreign key column to the Address table. So in my example, the Address table will have field fk_Client_id, the primary key of the client whose address this is, and also field fk_Vendor_id, the primary key of the vendor whose address this is. (Normally exactly one of these will have a value in it.)

Finally, if I want another kind of entity to have Addresses, say, Contact, then I can create another field in the Address table called fk_Contact_id and any value in this column is the primary key of the Contact whose address is this row.

Do you see any glaring problems with this approach?

Thanks for the help,

Steve

[SJM]

Thanks for the advice, but I cannot use the same table for both client and vendor. Because of the nature of our professional services business model, the structure of the two entities is radically different.

[Ender]

I feel like your proposal is slightly more complicated than necessary. I see now that, for each Table whose rows can have an associated set of Address records, I just add a foreign key column to the Address table. So in my example, the Address table will have field fk_Client_id, the primary key of the client whose address this is, and also field fk_Vendor_id, the primary key of the vendor whose address this is. (Normally exactly one of these will have a value in it.)

Finally, if I want another kind of entity to have Addresses, say, Contact, then I can create another field in the Address table called fk_Contact_id and any value in this column is the primary key of the Contact whose address is this row.

That was one of the options I suggested. This will work fine, but this creates fields that are only used for some of the records. Some would find this a little messy design. I've done it both ways, but I prefer to use one field for the foreign key and one for the type, and have the relationships filtered by type.