I have a main table for core items, and another which holds the components that make up the item. Eventually, we'd be looking at say, 20,000 items with 6 - 8 components each, meaning 160,000 or so records in that table.
I also have a system of letting users create their own items, using one of the inbuilt items as a guide. This data is kept in an equivalents table, which stored what the custom item is based on, what has been added, and what has been removed. A price is generated for custom items by using the base price of the inbuilt item + cost of added components.
But if a new inbuilt item is added or the price changes, I need to be able to pull out all the custom items, determine which base item is the best match, what additions and removals are needed, and what the new price would be. This part is pretty much complete though.
My issue is, when it comes time to work all this out, I need to know what components were in the custom item. At the moment I have to call the base item and it's components, then adjust it using the equivalents data, and then try and do a match.
Basically, I'm wondering if my best bet will be to keep doing this, or just to write the custom ingredients back to the ingredients table where they can be accessed easily. With this few rows of data, I am thinking that I probably wont take too much of a performance hit.
Any advice?
Thanks for reading by the way. I know it's a slog.