piet bieruik Posted February 12, 2014 Share Posted February 12, 2014 (edited) Hi everyone, After a long time im starting again with php and mysql i still have my doubts about the layout of the mysql tables I have a product1. shake 12. 500gram3. 26,50 4.banaan chocolateBut i have the same shake. but a different price with different flavors 1. shake 22. 750 gram3. 28.90 4. strawberry, mango, vanillaWhat is the best way to construct the tables?This is the site:http://www.scitec-sportvoeding.nl/winkelwagen.phpThanks Piet, Edited February 12, 2014 by piet bieruik Quote Link to comment Share on other sites More sharing options...
Barand Posted February 12, 2014 Share Posted February 12, 2014 You stand more chance of a response by posting in English on an English-speaking site Quote Link to comment Share on other sites More sharing options...
requinix Posted February 12, 2014 Share Posted February 12, 2014 Is it just me or is it that the amount and price vary based on the flavor? Table 1 for the shake by itself, and table 2 for the flavor, amount, and price - and a foreign key to the shake. products id | whatever ---+-------- 1 | shake 1 2 | shake 2 flavors? or maybe product variants? id | amount | price | flavor | product ---+--------+-------+------------+-------- 1 | 500 | 26,50 | banana | 1 2 | 500 | 26,50 | chocolate | 1 3 | 750 | 28,90 | strawberry | 2 4 | 750 | 28,90 | mango | 2 5 | 750 | 28,90 | vanilla | 2 Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 12, 2014 Share Posted February 12, 2014 I would interpret the "type" of shake as one that has multiple ingredients. So, a pinacolada would have coconut and pineapple. If that is the case, the tables might look more like below. This assumes that each product will have the same sizes available products id | whatever | ---+--------------------- 1 | shake 1 | 2 | shake 2 | ingredients (a list of all possible ingredinets) id | ingredient ---+------------ 1 | banana 2 | chocolate 3 | strawberry 4 | mango 5 | vanilla product_ingredients (which ingredients go with which shake) id | prod_id | ingredient_id ---------------------------- 1 1 1 2 1 2 3 2 3 4 2 4 5 2 5 product_prices (the available sizes for each product and the price) id | prod_id | size | price ---+---------+------+------ 1 | 1 | 500 | 26,50 2 | 2 | 500 | 26,50 3 | 1 | 750 | 28,90 4 | 2 | 750 | 28,90 Quote Link to comment Share on other sites More sharing options...
piet bieruik Posted February 13, 2014 Author Share Posted February 13, 2014 (edited) i have on stock: 1. whey protein prof 920 gram. strawberry 26,50 2. whey protein prof 920gram banaan 26,50 but i have also the same shakes but different weight(ofcourse different price) and sometimes a different flavor. 3. whey protein prof 5000gram chocolate 4. whey protein prof 5000gram strawberry etc maby like: id | name |price | size |falvor_id ---+----------------------------------- 1 | shake 1 |26,50 | 920gram|1,3 2 | shake 2 |90,00 |5000gram|2,3,6 Edited February 13, 2014 by piet bieruik Quote Link to comment Share on other sites More sharing options...
Barand Posted February 13, 2014 Share Posted February 13, 2014 That is one way NOT to do it. I'd go with something like this Product +-----+--------------------+ | id | description | +-----+--------------------+ | 1 | Shake | | 2 | Whey protein prof | +-----+--------------------+ | Flavour +---------------------------+ +----+-----------------+ | | id | flavour | Price | +----+-----------------+ +----+---------+------+---------+ | 1 | Banana | | id | prod_id | size | price | | 2 | Chocolate | +----+---------+------+---------+ | 3 | Mango | | 1 | 1 | 500 | 26.50 | | 4 | Strawberry | | 2 | 1 | 750 | 28.90 | | 5 | Vanilla | | 3 | 2 | 920 | 26.50 | +----+-----------------+ | 4 | 2 | 5000 | 115.50 | | +----+---------+------+---------+ | | | +-----------------------------------+ | | | Available | | +----+--------+---------+ | id |price_id| flav_id | +----+--------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 | 3 | | 4 | 2 | 4 | | 5 | 2 | 5 | | 6 | 3 | 1 | | 7 | 3 | 4 | | 8 | 4 | 2 | | 9 | 4 | 4 | +----+--------+---------+ Quote Link to comment Share on other sites More sharing options...
piet bieruik Posted February 13, 2014 Author Share Posted February 13, 2014 That looks good, but more scripting. So what will be the sql query. If i wanted to know all the weights with the right flavors of one product? Quote Link to comment Share on other sites More sharing options...
Barand Posted February 13, 2014 Share Posted February 13, 2014 SELECT p.description , pr.size , GROUP_CONCAT(f.flavour SEPARATOR ', ') as flavours , pr.price FROM product p INNER JOIN price pr ON p.id = pr.prod_id INNER JOIN available a ON pr.id = a.price_id INNER JOIN flavour f ON a.flav_id = f.id GROUP BY p.description, pr.size which should give something like this (no data so not tested) +----------------------+----------+-----------------------------------------+---------+ | description | size | flavours | price | +----------------------+----------+-----------------------------------------+---------+ | Shake | 500 | Banana, Chocolate | 26.50 | | Shake | 750 | Mango, Strawberry, Vanilla | 28.90 | | Whey protein prof | 920 | Banana, Strawberry | 26.50 | | Whey protein prof | 5000 | Chocolate, Strawberry | 115.50 | Quote Link to comment Share on other sites More sharing options...
piet bieruik Posted February 13, 2014 Author Share Posted February 13, 2014 Nice ! your a pro. Im starting an working with this. Thank you guys so much. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.