Re: Need advice on table structure

Good question! I suspect this is something most people come up against as their databases grow.

I'm going to encourage you to be brave, and put all these entities into the one table - persons, businesses, vendors, contacts, staff, clients, associates, ... They are, in fact, the same kind of entity. That's reinforced not only because they all use similar fields (names and contact details), but also because you need to be able to operate on them in the same kinds of ways (perform sales/purchases/payments, and hence have any of them as your foreign key.)

The fact that these entities belong in the same table does not mean you have to interface them that way. You can very easily create a query that shows only the employees, and use that as the RowSource of a combo box. And the events associated with that combo will open frmEmployee.

In cases where a combo contains all manner of clients, you will have a (hidden) column in the combo for the ClientTypeID. For an existing record, you read this column to determine which form to open. For adding a new entity in a box that could contain different types, there is no way to know which type the user intends, so you have to ask. You can do that with a dialog, or you could provide several buttons alongside the combo for "New business", "New associate", etc - as many as apply.

(In general, the NotIsList event is not applicable for any of these, since you have multiple pieces of info to garner from the user, and the NewData doesn't go into the AutoNumber column anyway.)

You may have seen this example before:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
The sample database (downloadable) illustrates the concept of putting the entities into the one table (since they are the same kind of entity), yet interfacing them with different forms (since that's how the user conceives of them.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cheese_whiz" <Cheesewhiz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:164F79E6-8193-491C-8DE0-26284102EE93@xxxxxxxxxxxxxxxx
Hi all,

I have an application that already has a contacts table and a 'facilities'
table (which includes everything from businesses to facilities within our own
organization). Both those tables have addresses. Now my 'clients' want to
add invoice functionality where they can input invoices associated with files
in the database. The invoices are paid to what they term as "vendors".

In the original design, 'facilities' weren't really used as entities
themselves, but always as foreign keys in a file, or as foreign keys in a
contact's record. A contact may or may not be associated with a facility,
and a file (files being the focus of this app) may or may not have a facility
associated with it.

Here's the rub: Vendors can be both people (contacts) AND businesses
(facilities).

I don't really like the idea of pulling all contacts and all people into a
single combo box due to the sheer numbers of choices that presents, plus it
seems like it would be a pain for me to address the 'not in list' if the
entry was a new one when you have essentially two different forms you'd want
to use. Mainly, it just seems wrong to me.

So, what are my options and, more to the point, how should I integrate
'vendors' into the existing setup? Should I just add a 'vendors' table and
have a third 'entity' type (contacts, facilities, vendors)? Could I
designate some contacts and some facilities as 'vendors' by adding another
field to the two tables and then use a query to combine them and some
additional step for the 'not in list' so that people could pick 'contact
vendor' or 'facility vendor' so the not-in-list uses the appropriate form?
Is there a better way?

Just a point in the right direction would be helpful. Thanks!
CW

.