[AccessD] Design Question

Given the possibility of having multiples, I would probably have the
individual object tables, then one big m-m "relationship" table where I
placed:

PEID
FACID
HDWRID
SFTWRID
SVCAGRID 


Probably you would want other info about the relationship such as when the
relationship was established and when the relationship terminated:

DteEst
DteTerm

"multiples" come from new records in the relationship table.

A person assigned two pieces of software would be two records, with just the
peid and softwareid, plus the DteEst.  When that person "turned in" the
software, the record's DteTerm would be updated.

The only issue with this kind of relationship is that one relationship could
be terminated while another was not.  If that is possible, then each
relationship needs to be established individually (separate records in the
relationship table), so that you can record when each relationship was
established and terminated.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bryan Carbonnell
Sent: Tuesday, February 06, 2007 1:14 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Design Question

I have a design question from a colleague that I can't answer. It may be
because I haven't given it enough thought, or that I'm too rusty when
designing Access apps, but here goes for you wonderful folks.

I have 5 distinct categories, which will be table(s) (normalized):

People
Facility
Hardware
Software
Service Agreements

So far, so simple, right?

Now comes the hard part, and where I'm having problems

Any one of the tables can be associated with virtually any other table.

For example:
A piece of hardware can be assigned to a facility or person A piece of
software can be assigned to a person or hardware or facility A service
agreement can be assigned to hardware, software, person or facility

These are just some of the combos that are possible. Multiples are also
possible.
Multiple pieces of hardware can be assigned to a single or many facilities
or people Multiple pieces of software can be assigned to a single or many
people / hardware or facilities Multiple service agreements can be assigned
to a single or multiple hardware, software, person or facility

How would you structure the tables to facilitate this? My brain can't figure
it out at the moment. I need to get back to programing and not management.
<sigh>

Any guidance or insight will be greatly appreciated.

--
Bryan Carbonnell - carbonnb at gmail.com
Life's journey is not to arrive at the grave safely in a well preserved
body, but rather to skid in sideways, totally worn out, shouting "What a
great ride!"
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com