Hi guys,

We have a mySQL database table for products. We are utilizing a cache layer to reduce database load, but we think that it's a good idea to minimize the actual data needed to be stored in the cache layer to speed up the application further.

All the products in the database, that is visible to visitors have a price attached to them:

The prices are stored in a different table, called prices . There are multiple price categories depending on which discount level each visitor (customer) applies to. From time to time, there are campaigns which means that a special price for each product is available. The special prices are stored in a table called specials.

• Is it a bad to make a temp table that binds the tables together?

It would only have the neccessary information and would ofcourse be cached.

-------------|-------------|------------ 
| productId  |  hasPrice   | hasSpecial
-------------|-------------|------------ 
  1          |  1          | 0
  2          |  1          | 1

By doing such, it would be super easy to know if the specific product really has a price, without having to iterate through the complete prices or specials table each time a product should be listed or presented.

• Are temp tables a common thing for web applications or is it just bad design?