Table Structure

[Justine]

Hello,

I am reconstructing a database I built a short while ago (not very
good). The purpose of the database is to track construction projects
and all costs associated with each project. Currently I have many
tables, but my concern right now is three tables I have:

ConsultantInfo, ContractorInfo, and VendorInfo

Whenever I send an approved invoice to our finance department for
payment I enter the invoice into an invoice tracking form. The costs
are then associated with the project. My problem is that we make
payments to consultants, contractors, and various vendors that are
neither consultants or contractors. I need to have a table to collect
contractor and consultant information (each set of information unique
from the other) so therefore the two tables. What I currently have
set up is redundant - I also have a vendorinfo table that contains
vendor (non consultant/contractor) information, as well as all the
contractors and consultants. I then use this table as a combo box in
my invoice tracking form.

Does anyone have any suggestions for what a better approach might be?
I thought about creating a query that contains the names from all
three tables and then creating a subform (which I don't really want to
do)based on the query to put on my form...Any suggestions??

Thanks in advance for any insight

Justine

 

[John Spencer]

I would think that you would have ONE table - ServiceSuppliers - that had a
field to identify whether the entity was a Consultant, Vendor, or
Contractor. I would think that the three types of ServiceSuppliers would
have many (maybe most) fields in common.

ServiceSuppliers
SupplierID
SupplierName
ContactFirstName
ContactLastName
SupplierAddress - Multiple fields
SupplierPhone - If you need a lot of different phones, this may involve a
new table.
SupplierTaxNumber (Taxpayer ID, SSN, etc)
etc.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Klatuu]

Although there may be some unique differences between consultants,
contractors, and vendors, I am sure (without looking) that most of the data
elements for the three are the same and probably used in the same way.

I would consider creating a table that carries all the common data elements.
I would also add a field that would identify what kind of supplier it is
(consultant, contractor, vendor). Then to store data elements that are
truely unique to a supplier type create child tables for each as necessary.

 

[Justine]

I would think that you would have ONE table - ServiceSuppliers - that had a
field to identify whether the entity was a Consultant, Vendor, or
Contractor. I would think that the three types of ServiceSuppliers would
have many (maybe most) fields in common.

ServiceSuppliers
SupplierID
SupplierName
ContactFirstName
ContactLastName
SupplierAddress - Multiple fields
SupplierPhone - If you need a lot of different phones, this may involve a
new table.
SupplierTaxNumber (Taxpayer ID, SSN, etc)
etc.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.












- Show quoted text -

Hi John,

Thanks for the response..Actually that is what I had originally, but I
changed it as I found the information I had to keep on consultants and
contractors (were they prequalified, what was their prequalification
date, expertise, etc.) was quite different and more extensive than the
information I needed to keep on vendors so I split the tables. I
can't think of a way to 'link' all the additional info I need to keep
on the contractors and consultants (which is different from each
other). Maybe I am missing something really basic?

Justine

 

[John Spencer]

See the response from Klaatu in this thread.

You can add an additional table that has the unique elements for a specific
type and link to that with a one-to-one join. This type of relationship is
sometimes known as sub-classing. So you might have two additional tables
for Contractors and Consultants to hold the fields that apply only to them.

That way you can get all service suppliers from one table and if you need
the "special" fields, you just use a join to the subclass table to get the
additional information.

Of course, you can simply include all the additional fields in the one table
and not fill them when they are required.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Justine]

See the response from Klaatu in this thread.

You can add an additional table that has the unique elements for a specific
type and link to that with a one-to-one join. This type of relationship is
sometimes known as sub-classing. So you might have two additional tables
for Contractors and Consultants to hold the fields that apply only to them.

That way you can get all service suppliers from one table and if you need
the "special" fields, you just use a join to the subclass table to get the
additional information.

Of course, you can simply include all the additional fields in the one table
and not fill them when they are required.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.









- Show quoted text -

Thank you for the advice. I decided to consolidate into one table and
create separate tables for the unique information.