[AccessD] Design Question

Hi Bryan:

It looks like an additional table would have to be create to store/keep the
relationships of facilities, people software etc...

People
Facility
Hardware
Software
Service Agreements

The above would be correct (I will assume that each record in each of the
tables has a field name ID) and if you add a table named 'CONNECTION' with
only two fields, additional to its own ID field. 

One field could be named OwnerID and the other field OwnedID. The records in
this table would be created when a relationship was established in code, one
for each relationship. The beauty of using this type of table is that
one-to-one, one to many, backwards or forwards and even reciprocal
relationships (the table could call it's self) could used...

The only particular about this type of table is that you as the programmer
would most likely have to maintain it with your code.... sorry not bound.
This system works great and I have used it to resolve many similar issues.

HTH
Jim     

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bryan Carbonnell
Sent: Tuesday, February 06, 2007 10:14 AM
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