0

I have a table of categories (currently with 14 rows, not likely to change). A need to cache combinations of categories emerged, so I'm creating a table of allowed combinations (only few hundreds of the ~10^10 combinations are likely to be ever searched for and I can constraint it even more). However, I'm not sure how to implement it.

Combination of 14 columns for each row in the category table looks simple, but it doesn't feel right. Any change in the category data would make a change in the combination table structure necessary.

Another option was to make the combination only with ID and combination name and store the important information in the category_combination join table. This feels "right", but making sure that each combination is unique becomes a non-trivial task (unlike simple applying unique constraint over a group of columns in the former case). In PostgreSQL, I would make a deferred trigger, but this is not possible in MySQL. The best option I can think of with MySQL is to make it a function and then calling it from the client app; if it finds a dupe, I'd cancel the transaction. I would aggregate the categories in the combination with GROUP_CONCAT (ordered, of course), either in the query or in an auxiliary view, and then compare the arrays.

Still, I'm not sure if there's no more elegant way, either a completely different approach, or an optimization of the second approach. How to do it best?

EDIT: category combinations will be often used in search, so it needs to be optimized for searching. Adding new a combination will be rare and I hope we will avoid adding new categories, though this should still be possible (even with a lot of work).

2 Answers 2

1

How about the SET datatype. With that, you could represent all combinations of 14 categories in 14 bits (2 bytes).

0
0

I realized one thing: I can easily obtain the ID of the combination by other means, so what I need most now is to obtain the array of categories, which is best done by the SET approach suggested by Rick James. However, I don't want to delete this my answer, because it best answers the question as written (especially the EDIT in the question). It's below.


I decided to pick my approach 1, the 14 int(1) columns. Why I think it's most efficient in my case:

  • I hope I will avoid adding new categories, so making a new column is not such a big problem. The second ("join table") approach would handle this seamlessly on database level, but I would still need to change my client app, so this is a marginal gain.
  • int(1) is not PostgreSQL's boolean, which is one bit, but still it's not so much bigger than the SET approach
  • adding new combinations is easy; in my second approach, I would need to insert into several tables
  • 14 rows is not so much, definitely not enough for some EAV-like approach
  • maintaining uniqueness is easy both in this and the SET approach, unlike the join table one
  • 14 ORs or ANDs per query is a lot, but still manageable
  • the SET approach allow for FIND_IN_SET search; however, searches based on SELECT with many ORs in the WHERE part are several orders of magnitude faster with proper indexes
  • I'm not sure about searching with the join table approach. Dynamic SELECTs constructed in the client can't find a single row, SELECT with many ORs is possible, and the GROUP_CONCAT approach I originally thought about would ignore the indexes. Perhaps there's something more elegant I don't know about, but I don't expect it to be more efficient than the OR search.
1
  • int(1) is 4 bytes. You probably want to use tinyint (1 byte) instead. Apr 24, 2015 at 14:43

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.