Dear All,
I am having a problem that you might be able to help me with.

I am making a small online game, to be deployed to a 1.5m user base. It's a kind of buying/selling game. In the game, there are three important entities, shop, user and item. Each user may only possess 50 or so items at once.

The problem is how to do the shop, user and item relationship. I would like the shop table related to the item table, so a shop can own an item, but then when a user buys an item, the item related to the user.

I suppose I am wondering whether to create shop_item and user_item tables, have multiple foreign keys in the item table (is this allowed)? or something like that.

Is there a right (or good way) to do this?


thanks in advance!

Tom

A "small" game deployed to a 1.5 M user base seems like a contradiction in terms to me!

You need to decide what the relationship really is before you can effectively decide how to implement that relationship. From the standpoint of ownership (in the real world), a shop is just a special case of a person. Is that what you want to model?

In most of the games that I've written, shops had infinite quantities of items (beats me how they managed that, they just did). If that is the case, you only need to track when a person "owns" the item.

Once you decide how you want the items to behave, the model should come to you pretty quickly.

-PatP