Need advice on table structure

[Cheese_whiz]

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

 

[Allen Browne]

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.)

 

[Cheese_whiz]

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

 

[David W. Fenton]

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 resisted this as well, but found that so many of my apps needed to
be structured in this way that I got over it!

What I found is that a People table works just fine. Now, how you
handle corporate entities is a complication. But the main issue is
that you should think about how your *application* uses the
entities, and not about what they are in real life. That is, a
company and a person are very different in real life, but if in your
app, they really aren't treated differently, then there is no issue
with modelling them as being the same.

 

[Armen Stein]

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

Yep. We've designed hundreds of databases, from just a few tables to
hundreds of tables, and these kinds of questions always have to be
answered.

(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.)

Generic NotInList logic might be too simple, it's true. But if you
want to provide add-on-the-fly for a mixed type table like this, there
are a few ways to do it. You can assume that the type is the same as
the current combobox is showing. And you can pop up a small form to
ask the user whether they a) just want to add this type to an existing
person they pick from a list, or b) want to add a completely new
person.

It takes more coding than you might want to do right now, but it is
possible.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com