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.Tent Size, I have the same values (20 x 20,...) and under PRICE_TENTS.Price 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.
2 1261
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, ItemAvailability, ItemOrderTime, ItemSetupTime, ItemReplacementCost, 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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: alex |
last post by:
Hi,
It seems like HTML 4.01 Transitional spec. doesn't allow table height
to be expressed in percents.
When i have this doctype tag:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01...
|
by: Toby Austin |
last post by:
I'm trying to replace <table>s with <div>s as much as possible.
However, I can't figure out how to do the following…
<table>
<tr>
<td valign="top" width="100%">some data that will...
|
by: mr_burns |
last post by:
hi,
is the table percent value for height used for displaying in browsers.
i have a table i want to run to the bottom of the screen so it seemed
best to set the height value to 100%.
when i...
|
by: Zak McGregor |
last post by:
Hi all
Are there any good solutions to aligning form field names and input boxes
without resorting to tables? I am struggling to do this nicely at the
moment.
Thanks
Ciao
Zak
|
by: Rick DeBay |
last post by:
I'm trying to create a layout table, where the spacing between rows varies.
I've tried using setting margin-top and border-top for the rows I wan't spaced
down from the one above, and I've also...
|
by: Neal |
last post by:
Patrick Griffiths weighs in on the CSS vs table layout debate in his blog
entry "Tables my ass" - http://www.htmldog.com/ptg/archives/000049.php . A
quite good article.
|
by: Michael Rozdoba |
last post by:
I'm far from a CSS expert, but what I see of it I really like & I love
keeping content & style separate. I also hate the way table layout
produces convoluted bulky code.
However when asked why...
|
by: Rob Freundlich |
last post by:
I have some servlet-generated tabular data that I need to present, so I'm
using an HTML Table. In some cases, it can be quite large. I'm flushing
the servlet output every N lines to push the data...
|
by: ALI-R |
last post by:
Hi All,
I have two user controls (header and footer) ,,which I've placed in an HTML
Table in a page.I set the **align="center"** in the table and the table
still is on the left side of the page...
|
by: phil-news-nospam |
last post by:
Is there really any advantage to using DIV elements with float style
properies, vs. the old method of TABLE and TR and TD?
I'm finding that by using DIV, it still involves the same number of...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |