JWColby
jwcolby at colbyconsulting.com
Tue Feb 6 12:30:06 CST 2007
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