hey, I guess this will be my first message on your fine forum.

I have 3 entities of interest, and I am wondering how to form the relationships.

I have a 'shop' entity, a 'user', and an 'item'. Items are related to shop, until purchased, then they are related to user.

My question is, how do I form the relationship? I thought I could do it in two ways;

1) have multiple foreign keys in item, so maybe itemid, shopid, userid, and only use one of shopid userid at once.

2) use some kind of link table with itemid, and 'parententityname' or something

I'm lost...

can anyone help?




thankyou !

Tom

If an item can only belong to one shop then the shop entity need not be related to item.

User is a many to many relationship with Item.

Your schema will therefore be:

Shop(shop_id, details)
Item(item_id, details)
User(user_id, details)
UserPurchases(user_id, item_id, qty, date)