How to manage products with different sizes and extras (1 Viewer)

[MetallSummer]

Hello to all,

I am making a database for one of my client and his products come with different sizes and extras. Each size has different price and the price of extras like cover, gift wrap etc. will also be according to the size of product.

I made two tables. Product and Product Sizes and then related them One-To-Many. I defined the prices in Product Sizes table accordingly. It went fine and then I made tables for orders. Again two tables with Order and Order Details and related them One-To-Many.

I dont know if I did it right or not because when I made a simple query and multiplied Unit Price with Quantity and I got strange results and I could not figure out where I made a mistake.

I would really appreciate if anyone can help me further.

Thanks in advance!

 

[theDBguy]

Hi. Welcome to AWF!

Seems to me Products and Product Sizes have a Many-to-Many relationship, because several products could have the same size. Can you post the rest of your table structure? Perhaps a screenshot of your Relationship Window?

 

[arnelgp]

if there is deviation in color, size, dimension or material you create a separated
ident code for it, example:

5mm concrete nail 1/2" long has different code against
5mm concrete nail 1" long

just make 1 table with ident code and full description, no need extra table for size, extras etc.

 

[Pat Hartman]

I disagree. You need a table for the product and its description and a child table for price by size. You could make a size table and then a junction table with the price in the junction table but I think that doesn't really get you anything here. I would just use size as a lookup table and leave it at that.

 

[arnelgp]

I disagree.

i totally Disagree with you.
I am material controller for more than 20 years and never seen such on real world. i don't live in imaginary world.

have you been in construction?
a single ISO drawing has Bill of Materials.
it clearly specify the color the dimension and material class
that a particular material.
there are 5 to 20 BOM on the drawing and none is separated by color.

 

[Pat Hartman]

@arnelgp
I have spent most of the last 50 years as a consultant and have worked in many different industries so although I am not a subject matter expert, I know a lot about construction and Bills of Material and ISO, and drawings. I don't believe this is that kind of application. The nomenclature used by the poster would have been different. This sounds much more like inventory control or order entry. In which case, you would have a product table that described the attributes of a product and a child table to identify the variations such as size and price. If you were to store the size and price with the product, you would have to duplicate numerous product attributes and .we all know how that violates normal forms.

For this application, you would use a cascading combo to pick the product and another combo to choose the size and therefore the price.

 

[The_Doc_Man]

@MetallSummer - it would appear that two of our members have radically different experiences here. Perhaps if you discuss you situation in a little bit more depth or give us some concrete examples of things that are "similar but different" then we might be better able to choose a direction for our advice. Also, be aware that when experts get together, they often disagree because both view things through the filter of their individual experiences. So you did nothing wrong by asking for help.

 

[MetallSummer]

Thanks for all replies. I really appreciate that. Let me clarify the situation through a simple example.

You go to a pizza place. They got one Pizza with 4 sizes and each size has its own price. Then you have the chioce to add extra toppings and the price for the extra topping depends on which size you have chosen before.

As a database builder, How would be best possible way to manage it?

 

[Pat Hartman]

Try reading #4 and #6 again. You pick pizza as the item and choose a size from the second combo. In the size table you should have a multiplier to be used in the calculation for toppings. The smallest size would have a multiplier of 1. Medium might have 1.3 and Large might have 1.5. As toppings are added, you multiply the cost of the topping by the multiplier associated with size.

To make the application easier to work with, all items will technically have a size because price is associated with size.

tblSizeType
SizeID (autonumber PK)
SizeName
Multiplier

tblToppingType
ToppingID (autonumber PK)
ToppingName
UnitPrice

tblProduct
ProductID (autonumber PK)
ProductName
etc.

tblProductSizes
ProductSizeID (autonumber PK)
ProductID (FK to tblProduct)
SizeID (FK to tblSizeType)
UnitPrice

tblOrderItem
OrderItemID (autonumber PK)
ProductSizeID (FK to tblProductSizes)
UnitPrice (copied from tblProductSizes because prices change over time)
Quantity

tblOrderToppings
OrderToppingsID (autonumber PK)
ToppingsID
UnitPrice (copied from tblToppingType because prices change over time)
Multiplier (copied from tblSizeType because this might change over time)

If you have to deal with tax and delivery charges, they go in the tblOrder.

 

[The_Doc_Man]

This is called an "Assembly" - i.e. a basic element plus add-on elements. The complexity comes from co-mingling the ordering process vs. the billing process. Start with an Invoice (customer ticket). Now have a function to add one of the pizza sizes. This includes, of course, the basics of cheese and tomato sauce. It ALSO has an item number (which could restart at 1 for each new customer.) Now add the toppings by name, which is easy - but have the order process check which item number is "current" and that way, see what size it is. Add the toppings by name and let the code behind the scenes figure out which price to charge, which is easy. Each topping has an entry in the toppings table with TWO keys - the choice of topping and the size of the pizza. Add those to the invoice and associate them with the current item number. Change to another pizza? Not an issue. Just add 1 to the current item number and start adding toppings based on the NEW size.

Now when you are ready to print the bill, you have the item keyed by its item number and the price comes from the size. The toppings are linked by item number (and have their own ingredient number, size code, and price.) The printout simply groups by item number with the toppings as detail items and the overall pizza is in the header.

 

[jdraw]

You may find google is your friend at least for ideas. I found these when looking for pizza restaurant data model

Pizza Restaurant database structure This didn't deal with topping price related to pizza size.
Database design exercise Pizza Shop This was an exercise with requirements - no solution.

 

[MetallSummer]

So, I tried everything according to my knowledge. Actually, if I do Quantity * Unit Price, it works fine but as soon as I add Quantity * Unit Price + IngredientPrice and run the query, it calculates as many as ingredients are there. I my case there are 29 ingredients.

The screenshots are attached.

Bundle of thanks in advance.

 

[arnelgp]

here is a simple example without much to do with "sizes".

 

[jdraw]

I looked at the Pizza database structure DDL from post #11 and pared it down some. I have not tested it, but will adjust as needed later. There are still some columns that should be removed since they are not needed to show concept (driver,pay_method,jurisdiction_id...). This is a first cut rough draft.

 

[jdraw]

@MetallSummer

What is status? Can you post a copy of your evolving database?

 

[MetallSummer]

@jdraw

Here is the copy of database.

 

[jdraw]

Your database is a different subject than discussed and I'm not sure of the details/requirements. If you keep with the Pizza theme as you suggested in post #8, and review 6, 8, 9 and 10 and set up a sample database, I'm sure you'll get responses and helpful advice. A sample test "Pizza" database and any resolving of issues may provide experience that can help to design and build your "real database".
I don't see a direct connection between "products come with different sizes and extras. Each size has different price and the price of extras like cover, gift wrap etc. will also be according to the size of product..." and the "meals" database you posted. I realize there is the concept of 'extras' being priced according to selected sizes, but there are requirements you are aware of that readers are not.
As has been noted in the thread, there are some differences of advice on how to approach such a design. Perhaps you have more details or description you could post.

You may also review and adapt the database provided by arnelgp in post #13 to your "real" subject matter using some of the design/approach advice in posts 9 an 10.

 

[oleronesoftwares]

Based on your explaination "
Thanks for all replies. I really appreciate that. Let me clarify the situation through a simple example.

You go to a pizza place. They got one Pizza with 4 sizes and each size has its own price. Then you have the chioce to add extra toppings and the price for the extra topping depends on which size you have chosen before.

As a database builder, How would be best possible way to manage it?"

Each pizza, i will name under pizza type, or Pizza group(this will be in a table called pizza_types, or pizza_categories

Each pizza size, i will name under pizza, with another column for the price and another column for pizza type(this can be a combo box that looks up from the pizza_types table)- this table i will name items

Then i will create two tables
1. Order table(this can have order_id(pkey),order_date, name of sales person, order_detailsid(fkey with order_details table)

2. Order details table- (this will have order_detailsid(pkey), order_id(fkey with order table), item(this will look up the pizza size and price,but will only show the pizza size), qty, price(auto calculate field to multiply the pizza price-from the lookup field's second column and the quantity field)


N:B Order table and Order details have a one-many relationship