I create a website where users can perform various actions, and they get a variable number of “dots” or “icons” to perform certain actions. Some data should be stored no matter what type of action the user performs, for example, user ID, type of action, timestamp, current total, and any icon assigned. But depending on the type of action that the user performs, some data of the type of action must be stored, including image data in the BLOB.
One option is to include all fields for all types of actions in the action table. Unfortunately, each of these columns will only store data for a small fraction of the actions corresponding to the corresponding type of actions. Thus, I would have a large number of empty fields (including BLOBs) with this approach.
Another option is to include a table for each type of action in addition to the above table of actions. Each action type table will have a foreign key for the corresponding action in the action table. This will allow you to better organize the action table, but it will add the possibility that the action table does not synchronize with the action type tables. I also wonder about the performance implications of having to make a large number of joins in different action type tables whenever I get data from an action table.
Finally, I optimize speed, not size. How do I approach this dilemma?
source
share