I'm new to database schemas and need some help.

I have to draw up a database schema from a large, (2601) possible product combination, with width and height and associated price.

Is creating columns form all the combinations the correct way?

closed as unclear what you're asking by Colin 't Hart, MDCCL, mustaccio, RDFozz, Gaius May 8 '18 at 21:32

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Could you gives to us more information about your needs? Such as what do you want to perform, what you already got, and what obstacles you have. – Alfonso Hernandez May 8 '18 at 13:17
  • 2
    And maybe give some example data. Give us contrived data examples of real data is too industry-specific for us to understand. – Basil Bourque May 8 '18 at 15:49
  • Apologies for the little information. This was the first time posting to this community. – swatch360 May 9 '18 at 5:45

Is creating columns for all the combinations the correct way? Based on the little information you have given us: No, columns for all combinations is probably not the right solution. Imagine if you suddenly get more products, then you will need to add more columns. Or if some products are removed. Designing your schema so you have to alter a table to deal with something as trivial is bad practice. No, you should probably use records instead.

Let's say you have a combination table, something like this:

CREATE TABLE combination (
  combination_id int unsigned auto_increment,
  product_id int unsigned,
  PRIMARY KEY (combination_id),
  CONSTRAINT combination_fk1 FOREIGN KEY (product_id) 
    REFERENCES product(product_id)

So you will insert a record for each product in a certain combination.

You can then select from this table, and join with your product table, to get a listing with all the combinations where a certain product is involved:

SELECT c.combination_id, p.product_name, p.width, p.height, p.price
FROM combination c
  JOIN product p USING(product_id)
WHERE p.product_id = 56
ORDER BY c.combination_id, c.product_id;  
  • Perfect thank you! Apologies for the little information. – swatch360 May 9 '18 at 5:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.