Table Layout

[Scotter]

Hi everyone,

I've been workin with my database for a while and I'm starting to run into problems with calculating totals and I think it has something to do with the layout of my tables. A rough layout of my tables are;

.                     /\------------------------\
(ITEMS)              |  \     (PRICE_TABLES)     |    (PRICE_CHAIRS)
|Order Number|       /   \--->|Price     |       \--->|Price     |
|Item        |------/
|Tent Size   |---------\
|Combo       |------\   \                                                    (PRICE_TENTS)
|Table Cloth |------\\   \---------------------------------------------------|Tent Size |
|# of tables |       \\                                                      |Price     |
|# of Chairs |        \\-----------------------------(PRICE_COMBO)
|# of Table Cloths|    \                             |Combo      |
                        \                            |Price      |
                         \      (PRICE_TABLE CLOTH)
                          \-----|Table Cloth|
                                |Price      |

30 mins later....

Ok, thats a very very rough look at what it looks like, if you still dont quite understand what I have going, then I would be happy to email a picture.

The reason I origionally did my tables like this is, Tables and Chairs I only have 1 type, no variety there, and the relationship to ITEMS.Item, which is a dropdown box that includes; Tables, Chairs, Tents, Combo, Table Cloth, is one to many, and the join type is "Include all records from 'ITEMS' and only thoose from 'PRICE_TABLES' and 'PRICE_CHAIRS' where the joined fields are equal.

ITEMS.Tent Size has 20 x 20, 20 x 30, 20 x 40, etc... for the different sizes of tents. So in PRICE_TENTS.Ten t Size, I have the same values (20 x 20,...) and under PRICE_TENTS.Pri ce I have the price for each size of tent. The situation is similar for ITEMS.Combos, and ITEMS.Table Cloths.

If you followed that I am pretty impressed. My problem comes in when I try to have field that has the "Total" cost for the order. I made an update query where I put in all the math and it updates certian records, but not all. Does anyone have any other ideas of how I can calculate a total cost for an order, or an idea for redesigning the layout of the tables?

Thanks for takin the time to read through this and thanks for any suggestions.

[Scott Price]

Hi there Scotter,

Your question is an interesting one from a database design standpoint!

To analyze what you currently have, you should first of all look for subjects, then characteristics of those subjects. Take for example the subject Items; for Items subject you have characteristics : ItemName, ItemPrice, ItemAvailabilit y, ItemOrderTime, ItemSetupTime, ItemReplacement Cost, ItemSupplier... etc etc etc (I've listed a few that you don't have listed :-) A second subject you show is Combos. This would have the characteristics of ComboName, ComboPrice, etc etc.

Each subject becomes a table, and each characteristic becomes a field in that table.

Remember that when linking tables in a One to Many relationship you put the linking field into the table on the Many side of the relationship.

Now to fill each field with test data to 'test' our design concept: ItemName has these items: 20 x 20 Tent, 20 x 40 Tent, Plastic Chair with arms, Plastic Chair w/o arms, 2 x 3 Table, 3 x 4 Table. ItemCostPerDay has these: $100, $150, $1.50, $1.25, $5, $5.50... Etc etc etc.

Now, you can reorganize all of the tables you mentioned in your post into two! tblItems and tblCombos :-)

The benefits of doing this reorganization of your data will be: more flexibility, less redundant data, easier data manipulation, less grey hairs troubleshooting , easier upgrading, and so on!

Hope this helps you a bit,
Regards,
Scott

[Scott Price]

Thinking this over again last night I realized that you will actually need 1 more table. The relationship you have between tblItems and tblCombos is Many to Many, so for a Combo called Package1 you may have 1 20 x 40 Tent, 10 3 x 4 Tables and 100 Chairs. More than one Item can be related to One Combo, and conversely More than one Combo can be related to One Item.

Therefore making a linking table called tblComboItem with three fields is the best way to go:

tblComboItem
ComboItemID AutoNumber PK
ItemID Number FK
ComboID Number FK
Quantity

Regards,
Scott