[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.