Hi everybody,
I am in the process of creating a virtual items trading system with the following kinds of tables: user_items, items, trades_lots, trades_offers. Items is the database of virtual items and user_items is who has what items (columns: user_item_id, item_id [corresponding to items], user_id [who owns the item]).
trades_lots is where users can create lots of their user_items for trading. trades_offers is where other users can offer on said trades_lots with their items.
I am trying to think through trades_lots and trades_offers particularly. What do you think is the most efficient way of storing which of their user_items are contained within the lot? The reason I’m asking is because in the past I’ve created single columns like item_1, item_2, item_3, item_4, item_5, etc that would contain a corresponding user_item_id in trades_lots. But expandability becomes a problem when data storage is contingent upon the number of physical columns in the table schema.
Something is telling me that this isn’t the most efficient way. Would a “lot mapping table” (i.e. trades_lots_items) actually be better for the model whereby trades_lots_items contains which user_items are in the lot?
Just thinking out loud and looking for the most efficient design solution. Thanks in advanced.
