Re: Need advice on table structure

Thanks for a thorough response, Allen. I never even thought about putting
everything into one table before. Still being pretty new to Access
development, I've been more worried about using too FEW tables rather than
too many.

I've downloaded that app, and I'll take a look at it. I wish I had seen
that before I got so far into it. I've been all over your site (and several
others), but I've seen so many samples at this point I can't remember
everything I've seen or where it is even if I remember it! If I had to start
this whole journey over again, I'd start with a database to track all the
samples and help tips out there!

Oh well, live and learn.

Thanks again!
CW


"Allen Browne" wrote:

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


.