Relationship? Clients, Vendors and Documents tables

[scion]

I have 3 tables - CLIENTS, VENDORS and DOCUMENTS. For the sake of
simplicity, let's say each table has the following fields:


CLIENTS: ClientID, ClientName

VENDORS: VendorID, VendorName

DOCUMENTS: DocumentID, DocumentContent


How would I setup a proper relationship if:

1. The Document may be sent to a Client, but not to a Vendor.
2. The Document may be sent to a Vendor, but not to a Client.
3. The Document may be sent to BOTH a Vendor AND a Client.

Note: The Document MUST be sent to AT LEAST a Vendor or a Client.

Thought this implied a Junction table, but can't figure out how it
would work. I thought about putting the ClientID and VendorID fields
into the Documents table (as foreign keys), but I've read that foreign
keys should always be set to REQUIRED, so as to prevent orphaned
records.

But I can't set BOTH the VendorID and ClientID foreign keys to
REQUIRED, since that would mean that the Document would HAVE to go to
both the Vendor AND the Client.

Am I missing a simple concept? (wouldn't be the first time :-0)

 

[Roger Carlson]

No, a Foreign Key does not have to be Required. However, if you are using
Autonumbers for the primary keys and Long Integers for the foreign keys, you
should delete the 0 (zero) from the Foreign Keys Default Value.

The big questions is if a Document can go to ONLY one Vendor and/or ONLY one
Client. If so, you can add ClientID and VendorID to your Document table.
To make sure you have at least one vendor or client, you can add a
Table-Level validation rule like:

[VendorID ] Is Not Null Or [ClientID ] Is Not Null

That will ensure that at least one of them is filled in. However, this will
mean that you cannot create a document without assigning it to at least one
Client or Vendor.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[scion]

Thanks for the reply Roger - very good insights!

Made me think... I guess it's possible that a Document would go to more
than one Vendor (example: a generic request for an estimate).

Unlikely that a Document would go to more than one Client for my
purposes, but I guess that's possible too (example: I decide to create
my own Holiday Card as a Document).

If I throw those variables into the mix, what happens?

 

[Roger Carlson]

Well, if a document can go to one or more Vendors and each Vendor can
receive one or more documents, then you have a Many-to-Many relationship.
To implement a M:M in a database, you have to create a linking table (also
called an intersection table) that holds the foreign keys of both tables (ie
DocumentID and VendorID) and create two One-to-Many relationships between
the two base tables and the linking table.

Now that I think about it, why do you even have separate Client and Vendor
tables. They store essentially the same information, don't they? Names,
addresses, etc? Why not have a Contacts table and have a field that
differentiates the Contact as either a Vendor or a Client? That way, you
can simply have the single Many-to-Many relationship.

I think I'd also drop the requirement that a document MUST be assigned to a
vendor or a client. As I said, you won't even be able to create the
document record in your database without first having someone to assign it
to. There may be times when you want to create a document before the vendor
or client record is created.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[scion]

Excellent points... I originally thought it best to keep my Vendor and
Client tables separate, but as both tables essentially store the same
info (as you pointed out), I'm going to rethink my design, as it would
definitely simplify the management of realtionship to the documents
table.

Thanks so much for your time and assistance!