Lawrence Mrazek
lmrazek at lcm-res.com
Fri Mar 28 11:36:58 CDT 2008
Hi All: (I'm just beginning to tackle this today, thanks to all who have responded (Susan H. & Robert S.) I think Drew's approach probably fits this problem the best ... This way I'm not constraining the client, and it will allow them to better manage the data on their own. The only other issue that I face is customizing the data input experience for each specific field ... Basically, what if I need to have a radio group or dropdown list? Larry Mrazek LCM Research, Inc. www.lcm-res.com lmrazek at lcm-res.com ph. 314-432-5886 mobile: 314-496-1645 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka Sent: Friday, March 28, 2008 11:09 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] DB Design Question I would recommend designing the 'service groups' as a table design in and of itself. For example, you have multiple companies, each with multiple locations, each with multiple 'service groupings'. Each service grouping could have it's own fields. Instead of making those table fields, make them data fields: tblServiceGrouping ServiceGroupingID ServiceGroupingName 1 Dogs 2 Cars tblServiceGroupingFields FieldID ServiceGroupingID FieldName FieldType 1 1 Breed Text 2 1 Color Text 3 2 Make Text 4 2 Color Text 5 2 HorsePower Integer tblSGDataText FieldID NormalDataID <-- This is going to link the data to what you actually want to record.... Data (Text) 1 'x' Terrier 2 'x' Brown 1 'y' Dalmation 2 'y' White tblSGDataInteger FieldID NormalDataID Data (Integer) 5 'z' 250 5 'a' 300 I have several systems where I needed 'dynamic' table designs. This method works pretty well. That 'NormalDataID' is the id that is going to link the data in the 'data tables' to what you are trying to accomplish. For instance, the sample data I posted above, let's say that you are recording an 'customer' for each service Grouping. So you would have Customer X, which has a brown terrier. The Customer ID (or better yet, the Customer Service ID (so a customer could be serviced more then once), would be the 'NormalDataID'. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lawrence Mrazek Sent: Wednesday, March 26, 2008 3:52 PM To: 'Access Developers discussion and problem solving' Subject: [AccessD] DB Design Question Hi Folks: I'm working on revising a resource directory consisting of companies/organizations and the services they offer, and would appreciate any feedback regarding a specific design question I have. Each company/organization can have multiple locations. Given that the locations are currently contained in 20+ separate tables, once for each service grouping (EX: dogs, cars, birds), and with specific fields for each service, I'd like to resolve the locations into one table, and link out to services via a some sort of relationship. For example, the "dogs" table might have the additional fields: breed, color, weight, while Cars might have: horsepower, tire size, etc. I'd like to have this system to be as flexible as possible, any hints on how to relate those service specific fields to the general tblLocation? Note that each location might could have multiple services associated with it. Thanks ... I'm just a bit foggy today and need a second opinion on this problem. Larry Mrazek LCM Research, Inc. www.lcm-res.com lmrazek at lcm-res.com ph. 314-432-5886 mobile: 314-496-1645 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com