0

I have a table products:

#products
ID | category | type | criteria1 | criteria2

, with category and type being foreign keys of other tables.

Should I split this table up into category1_type1_products, category1_type2_products and so on? It seems to me I should, because right now in my table, there are some thousands of entries with the same ID and category value. Loads of redundand information.

Also, a query could perform faster if mysql didn't have to search for all rows with the specific category and type first. (right?)

Is this recommended structuring behaviour at all? If not, what if my table has got 5 million tuples?

1 Answer 1

1

No. That would mean the table NAME contains DATA - you then have to modify the database structure (create new table) just to add one more category or type.

If your category/type column is tinyint or smallint (depending on reasonably expected number of possible values), then you actually have proper normalization in place. If there is no (functional) dependence between two keys to categories for two different rows then those two values are not a redundancy but a minimal way to store the actual data.

From performance standpoint the important part is proper indexing for your queries. If you often search for rows with specific category and/or type, you should have in place index on that column (or composite index on both columns, and possibly other) to optimize such queries. You should enable the slow query log and periodically check what queries are taking most time (you may use pt-query-digest to analyze the log) and optimizing those (adding proper indexes and/or rewriting the queries).

When you have proper indexes in place then in your proposed case of multiple tables actually selecting the right table to read will take about the same time as MySQL needs to "jump" to the right part of the index. (It is a bit oversimplification but the point is that selecting the right table to read is an overhead in itself).

From the "schema" standpoint I would be more concerned about those two columns criteria1 and criteria2 if these are their real names and usage then that seems like a possible problem - but that may depend on your use case and may be actually OK. But if there is any chance you will once add criteria3 and others then that would tell you to add separate table for those, one row per criteria and FK to the current table.

9
  • Thank you, your answer was 110% helpful to me! I actually plan to add criteria dynamically from time to time. However, I don't quite understand why I should put them in an extra table. I still need them as columns in the current table anyway..? still this wasnt even part of the question, so thanks again
    – phil294
    Sep 13, 2015 at 18:25
  • If you need multiple columns like these and they are of the same type, then you just remove them from this table and create a different table with one column of the needed type and one column as FK to the current table (each "criterion" knows which product it belongs to). Then you might want to add either for instance some auto_increment column to work as a primary key OR an "order" column containing a number telling if "criteria1" or "criteria2" etc is stored in that row. You then have multiple rows in that table for each product (row) in the original table. ...
    – jkavalik
    Sep 13, 2015 at 18:30
  • ... You can query them separately (select ... from product_criteria where product_id IN (<products you are interested in>) or by joinin the proper "order" criterion to the product row (if you know which one or some constant number of them you really want). If you try to join then to your "original" product query, it will repeat the product row multiple times and adding one of the associated criteria to each one - multiplying the number of returned rows - that may be used in some cases but is a bit cumbersome to pull of in others.
    – jkavalik
    Sep 13, 2015 at 18:34
  • this took me a while to understand, but I'll try it out. cheers and good night
    – phil294
    Sep 13, 2015 at 19:06
  • no, I don't get it. I obviously need these criteria columns as... columns. Every product should be able to have a value for each criteria, as you said, with a junction table. But if I want to get a table which includes some products and their respective criteria data, I need to have criterion1,criterion2 and so on as columns. But by using a JOIN, the criteria IDs will come out as rows, right to the product table. I probably misunderstand something here
    – phil294
    Sep 16, 2015 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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