table structure help

bjackson

Registered User.
Local time
Today, 20:21
Joined
Jan 3, 2003
Messages
404
i am trying to set up a db for purchasing laminates but cant get my head around the right way to structure the tables.
Each order would consist of
1. selecting a supplier
2.selecting a brand of laminate from that supplier
3.selecting a colour from that brand
4.selecting a finish for that colour

That part i can understand,but where i struggle is
once that product has been selected,you then have to select a sheet size
that the supplier carries,which varys depending on what laminate was selected,and the price of the laminate selected may or may not vary depending on
1.the sheetsize
2.the qty ordered, eg 1-5 sheets $10 sq/m, 5-20 sheets $8 sq/m

also in a range of 2000 colours 500 of those may all be the same price structure so how could you update the prices in 1 place only

can anybody suggest the most effecient way to organise the tables so that the user has the minimal data entry.
many Thanks in advance
 
Cool db.

To do this without having nine million posts bouncing back and forth, I would suggest you go through the agony of documenting your business rules for this entire process. You have already provided enough info to get about half of it down. You game or had you rather just get some tips and wing it?

ken
 
Well i was hoping to get some tips on what extra tables would be needed other than the 4 i have named.I guess i would need a table for sheet sizes and some sort of pricing table but i am not sure on how to relate these tables to the laminate selected,and of course an order table.I am not well versed at the business rules part so i guess if it takes 9 million posts then
as long as the members here at AccessWorld dont mind, then i will just have to keep on asking questions.I have read a lot of threads re table normalisation and would like to get mine set up properly but as i said before i cant quite grasp the concept.I am one of those unfortunates who needs to see something work to grasp the how!


Regards
Bjackson
 
On the surface what you have looks very similar to what we use to calculate prmium rates for the different products offered by life insurance companies. By that, I mean you have certain base price/product type and then variations on the base "price rate" determined by order size and so on.

If you have a huge number of base "price rates" as we do then the first thing I would check is if the various suppliers will give you these on disk. If they do/will then that in itself might determine how you form your tables.

From what you have said I think you will have two many variations to get the final result on a query row. I use DLookup in macro SetValue actions and while there will certainly be nicer or better ways, the principle will be the same, that is, retrieving a rate/price from a table and then applying factors that are based on the entries made on a form. Whether you use code, macro or DLookup the principle would be the same in that a series of them will run depending on the entry on the form.

While it hardly meets normalisation I would also consider having a table for each supplier and their pricing structures. For insurance we use a completely separate system for each company although the premiums/benefits that result are appended to the one table. If the laminates you buy from your different suppliers have a lot more variation than just price then you might find different setups for each supplier will be the easiest way to go. You did mention that out of 2000 colours there could be 500 with the same price so that means 1500 different prices and to me that would mean definitley looking to see if the supplier will give you those on disk. If so, then this is where separate setups for each supplier might be the easiest way to go.

You might also look at the Northwinds sample data base that comes with Access for some ideas as it has an orders forms and subform.

Mike
 
Forget the separate table for each company suggestion, it's absurd.
 
Rich said:
Forget the separate table for each company suggestion, it's absurd.

Dear Rich,

Whether it is absurd or absurd will depend on what sort of pricing structures he might get or might not get from his suppliers.

For example, in our case we will get a table from the insurance company as there are rate and product changes. We then just replace the table. To make them all into one table would take forever.

A small example. Company A runs down a column on age next birthday for smoker, non smoker, male and female. Company B has a field for each of those rate factors. Company C has a column for male non smoker and then columns to apply a factor for smoker and smoker female and non smoker female.

Mike
 
Mike375 said:
Dear Rich,

Whether it is absurd or absurd will depend on what sort of pricing structures he might get or might not get from his suppliers.

For example, in our case we will get a table from the insurance company as there are rate and product changes. We then just replace the table. To make them all into one table would take forever.

Mike
stop talking out of the wrong end of your body, we're all bored to tears with your spreadsheets :rolleyes:
 
Crikey,i just wanted to get some help, not start WWIII.
perhaps i should withdraw the thread :eek:
 
bjackson said:
Crikey,i just wanted to get some help, not start WWIII.
perhaps i should withdraw the thread :eek:
nah, there's just a disruptive poster here who wants to justify his macro driven spreadsheets
 
Rich said:
stop talking out of the wrong end of your body, we're all bored to tears with your spreadsheets :rolleyes:

Dear Rich,

Try and stop reading what bores you. In addition why don't you try and post what you think can be done rather than posting comments as to what you think won't work.

Spending your life reading what bores you and only commenting on what you think won't work is bad for your health and very bad for eanring income.

Have a nice day and hang in there :D

Mike
 
Now i know what KenHigg meant "You game or had you rather just get some tips and wing it"

Well it would seem you 2 guys agree to disagree and look like you may have a history of that , but that hasnt really helped what i am trying to do.I really would appreciate some help.
I'll try to explain my situation a little more .
At present i have about 15 suppliers,1800 laminates and 108 sheet sizes.That equates to approx 10,100 sheets to select from and price
The suppliers price lists are in paperback form not disc(unfortunately)
the laminates are priced by the square metre
each supplier has max 10 price categorys ,and within these categorys they
have max 4 price rates based on qty orderered.To complicate matters, some of the smaller sheet sizes are priced on a higher sq/m rate

there must be some way of linking all this up and getting a query to return the price of the sheet selected, or will i need to price 10,100 sheets (surely not).As i said before its the pricing side of the equation that i need the help with.

i have attatched some sample data in the hope that some one may wish to help me.These are tables that i have to link to in a third party vb application so i have limited control of the table fields,but i am able to add some if required

Mike375. I thank you for your input but i really do want to stick with normalising as much as is possible,because i believe that this will get me the most effecient way of maintaining 10100 prices

Rich, i have read a lot of your threads and you obviously know your stuff,but Mike375 has a point,pointing out whats absurd while not offering any other suggestion seems to me to be a bit counter productive. :)

Regards
Bjackson
 

Attachments

bjackson said:
Rich, i have read a lot of your threads and you obviously know your stuff,but Mike375 has a point,pointing out whats absurd while not offering any other suggestion seems to me to be a bit counter productive. :)

Regards
Bjackson

The village idiot has argued with everybody here,so there's no point in offering advice. He will argue otherwise and go into another diatribe on what his db does. :mad: post a 97ver and I'll take a look, unless you'd like a macro driven spreadsheet :eek:
 
Rich said:
The village idiot has argued with everybody here,so there's no point in offering advice. He will argue otherwise and go into another diatribe on what his db does. :mad: post a 97ver and I'll take a look, unless you'd like a macro driven spreadsheet :eek:

:D :D

14,007 postings and counting.........
 
Rather than assigning a price to each item, assign a category as the supplier does. Then use the category and the sheet size to look up the price. So you'll need two tables, the category table and the sheet size table. If the groupings are similar for all suppliers, use a single set of categories that cross supplier boundries. If each supplier's tends to be different, include supplierID in the category table so you can have a separate set of categories for each supplier.
 
Rich, i dont want a spreadsheet,because thats sort of where i am at the moment,so heres the 97 version, and thanks in advance for any suggestions.

Pat,I know this is really simple database stuff, but i am struggling to understand in which table the actual price rates would go.Each supplier tends to have similiar pricing structures but i would like to try to incorparate the flexability to plan for suppliers who have different price structures. When it comes time to update prices to reflect suppliers price increases(which occur far to often) then i dont want to have to spend days updating data.
Would the categorys table look like

CategoryId
SupplierId
QtyRate1
MinNumberSheetsQty1
Qtyrate2
MinNumberSheetsQty2
Qtyrate3
MinNumberSheetsQty3
Qtyrate4
MinNumberSheetsQty4

Am i on the right track ?

Regards
Bjackson
 

Attachments

Sort of. You just need to normalize the structure.

tblCategory
CategoryID (autonumber primary key)
SupplierID (foreign key to supplier table)
CagegoryDesc

tblSheetSize
SheetSizeID (autonumber primary key)
SheetWidth
SheetLength

tblCategoryPrices
CategoryPriceID (autonumber primary key)
CategoryID (foreign key to tblCategory)
SheetSizeID (foreign key to tblSheetSize)
LowQty
HighQty
PricePerSqMetre

When you need to look up a price, you'll need to join to tblCategoryPrices using a non-equijoin. To do this create the query normally and draw the join line from the categoryID to the CategoryID and from the Qty to the LowQty. Then switch to SQL view and change the join:

From YourTable as T Inner Join tblCategoryPrices as P ON T.CategoryID = P.CategoryID AND T.Qty >= P.LowQty AND T.Qty <= P.HighQty

Essentially you are looking for the price record where the quantity that is being ordered is between the low and high value for the sheetsize.

Build the tables and put a few rows of data in them to see how this works.

As to flexibility, this method will give you all the flexibility you need as long as:
Price is calculated based on the size of the sheet and the number of same size sheets ordered. If anything else affects the price, such as color, you need to account for that in the pricing structure. Color will greatly complicate the pricing so I would assign the specially priced colors to a different category.

If in the future, some other factor is added to the pricing equation, you may have to re-do the pricing if you can't make the new factor into a category of its own.
 
I would look at moving the pricing mechanizam to function.

Something like price(a,b,c,..)

Where 'a' is a pricing code, b is qty, etc... And the pricing code is used to tell the function which formula to use and is stored with the sheet info.

???
ken
 
Thanks Pat & Ken

It will take me a while to digest your suggestions,I will get back to you with my results (or further questions)

Regards

Bjackson
 
Pat
I set up tables as you suggested,which helped me realise that possibly i needed another table to reduce the amount of times i would need to enter
the same price.When i started to fill a few rows of the table tlbCategoryprices
it dawned on me that for every sheet size(108 of them) i would need a sq/metre rate,multiplied by the number of suppliers.I created a table of Sheet size categorys and added a key field to sheetsizes table.However i still cant seem to relate all the information.The query i have built returns the right category price but for all the suppliers.
Have I gone off on the wrong tangent,or should i follow your instructions to the the letter.I dont want to be someone who asks for help but then wont listen.
I have attatched a 97 version of the data and the query. I had to upload it in 2 parts

Regards
Bjackson
 

Attachments

Users who are viewing this thread

Back
Top Bottom