large project! need advice on schema

[wendyd]

I'm hoping some of you access geniouses can give me some advice!

I've got a fairly decent size project to complete and need some suggestions since this isn't my area of expertise.

This is a database for sales people to enter orders and print reports. The products list is large. There are several different groups of products that are broken up into more groups. We're talking a lot of products here!

I've got about 10 different order forms. All for different types of products. Many of the fields on the forms are similar but the order details vary. For example, one form has 3 different boxes for the salesperson to enter different months the product is to be shipped. Another form has 3 different package types, etc...

The way I have the database set up is I have one main order table (which holds the order number and header info) and 10 different order details table (which holds line item details. One details table per order form).

My question is, would it be better to have just one order table that relates to one order details table and just include all fields for every form in the one order details table. Some orders will use some fields and others will use other fields. I'll have null values for some fields in each record because the orders won't require the same information.

Is this a good idea or should I just stick with different order details tables?

Right now I'm looking at 28 tables total.

Thanks! Another note, this site has been a great resource for me!!! Thank-you for all the wonderful posts!!

- Wendy

 

[katiekat]

I would think that breaking it up would be the way to go, personally, but I might be wrong. I'm no access genius either. If you know that some fields aren't going to be needed for some reccords, then it seems like a waste of space to associate them. As long as the reccords are connected by some kind of primary key, you should be able to do everything you need to.

Good luck, sounds like a fun project!
Kate
Holy tek-tips batman!:-0

 

[JoeMiller]

The singular route, one orders table, one details table is MUCH easier to maintain. You may want to look at how the tables relate as far as requirements though. What I mean is, say you have two details table. One is for Widgets and one is for Wonkers and lets say they can have part numbers that are the same (WidgetID=WonkerID). An order for widgets requires a detail line to have an OrderID, WidgetID, Descr, Qty, SalePrice. An order for Wonkers requires a detail line to have an OrderID, WonkerID, Descr, Qty, SalePrice.

In this example when we're dealing with the each table we know one is for Widgets and one is for Wonkers. But to make this more efficient we can add another field, let's call it DetailType and then have one table do all the work. So that details table has the following fields:

OrderID
DetailType (Widget or Wonker)
ProductID (WidgetID or WonkerID)
Descr
Qty
SalePrice

Now when you do reporting you can use the detail type to eliminate records you don't need. Looking for Widgets? Set the DetailType to Widgets or vice versa. As for having fields that are unoccupied, this isn't a major concern unless you have MANY MANY fields that are not capable of supporting dual capabilities.

The easiest way to look at the similarities (for me at least) is to go into excel and put down a row for each table I'll need with the table name in the first column and the fields in columns 2 and up. Then I look at the similarities and find a way to set up another field that can be a logical switch (like DetailType) to trigger me knowing when DetailType has this value, field x = y and when DetailType has this value, field x = z.

My two cents, hope that helps! Joe Miller
joe.miller@flotech.net

 

[PaulF]

you should spend some time going through the Northwind.mdb that comes with Access, it does what you want to do, and there's plenty of examples for you to see and use/rewrite to fit your needs.

PaulF

 

[wendyd]

Thank-you for the great replies! All very good suggestions! I like the idea of having two tables and I like the idea of keeping it simple. It doesn't look like I'll have too many extra fields. Maybe 10 max. I just have to make sure that the fields aren't required in the table. I can make them required on the form. I've been looking at all these products and their forms to figure out the similarities and differences. I think the suggestion to include a DetailType is a good one!

I was concerned about good database design. I would hate to have to reengineer the database later on.

Thanks again for the wonderful insights!!

- Wendy