linking two tables from one field

R

Rick Bailey

I have a person table and a business table that I (think I) would like to
keep separate. But I want to able to create an invoice (or similar) that has
a customer field that can point to either a person or a business.
This is just one example of this problem, so I'm looking for a general
solution. How have other people (or businesses :)) done this?
 
A

Allen Browne

Rick, I strongly suggest creating one table to hold both.

For the simple example that you gave, it's very easy to use an IsCorporate
field (yes/no) to distinguish them. The MainName field contains the surname
for personal records (IsCorporate contains No), and the company name for
businesses (IsCorporate contains Yes). There are some fields that apply only
to personal clients (FirstName, BirthDate, ...).

For the interface, you can use 2 different forms so that it looks like two
different "tables" to the end user. For personal clients, the form's
RecordSource will be:
SELECT * FROM tblClient WHERE IsCorporate = False;
But because they are all in the one table, you don't have any of the
problems of trying to UNION different tables.

If your actual case is more complex where there are many divergent fields
between the two or more types, I would still encourage you to put the common
fields into one table, and then put the various divergent fields into
further tables, related 1-to-1 to the common table. However, you should
first try to get around this by creating a related table to hold these
various extra attributes - fields like:
ClientID foreign key to the common table.
AttribTypeID select from the kinds of things that could apply.
AttribValue the actual value for this attribute that applies to
this client.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top