DB Design Question

[rparker85]

Hello!
I’m fairly new to Access and I’m trying to determine the best way to setup my database(s). The data I’ll be entering is variable contact information (up to three addresses), order information based on client, and price information based on order and client. I’ll also be creating a verity of reports and the data will be entered through a form. For example:

Order123:
CustomerA makes an order under ClientA for PartA, PartB, and PartC/D .

I’ll need to enter the following information for a single order:
Client(Store)
-CustomerA
Address1 (Variable)
Address2 (Variable)
Address2 (Variable)


-Recipient of Service (Recipient will always be a minimum of 1, but could be up to 10 per Customer order)
-PartA
Service Name (Variable by Client)
Service Type (Variable by Client)
Service Level (1, 2, 3, or 4 – Same across all clients)
Cost of Service (Variable by Client)
-PartB
Service Name (Variable by Client)
Service Type (Variable by Client)
Service Level (1, 2, 3, or 4 – Same across all clients)
Cost of Service (Variable by Client)
-PartC
Service Name (Variable by Client)
Service Type (Variable by Client)
Service Level (1, 2, 3, or 4 – Same across all clients)
Cost of Service (Variable by Client)
-PartD(Part D is tied to part C. If a customer orders C they will automatically get D)
Service Name (Variable by Client)
Service Type (Variable by Client)
Service Level (1, 2, 3, or 4 – Same across all clients)
Cost of Service (Variable by Client)

Right now I have three databases:
-Order Database (which stores all my variable data mentioned above)
-Store Database (Which stores all of my static client/store data. I have multiple stores in this single database and it’s linked to the Order DB by store name.)
-Service Database (This stores all of my static service data. The service names can be different by store but it’s the same standard four services for each. This is linked to the Order DB also by client name)

I’m trying to figure out if this is the most efficient way to handle this. I’m wondering if I should have the Recipient of Service in a separate database that is linked to the Order database by record instead of trying to store it within the Order DB. Is it better to store data within one single database or have several smaller databases that store all the data and have it linked together? When I’m creating reports I’m having them based off of data that is pulled from queries. Thanks in advance for the help and I’m sorry if none of this make sense, like I said I’m new to Access. J

[orange]

There is a tutorial at this site that could help put the various pieces together
http://www.rogersaccesslibrary.com/T...lationship.zip

There are a number of free data models at this site that may help you
http://www.databaseanswers.org/data_models/index.htm

The best advice I can give is
-make sure you really understand the "business rules" you are trying to support,
- the ease of use and life expectancy of a database is directly related to how well the underlying database structure matches the business,
- if you're new to database, go to the tutorial and get an understanding of the process and terminology involved in database design,
- look at the free models that have similar tables to what you envisage, and see how they are related. The sample models are only samples for reference. It is unlikely any model will satisfy your needs, but 1 or more may give you some ideas.

Good luck.