Designing many to many relationship between three entities

Question

I'm having trouble modelling this situation. I'm designing a location-based coupon system. A user can define zones and offers and then associate each offer to multiple zones. Also each zone can have more than one offer. So zone and offer have a many to many relationship. The user entity owns both entities. A zone and an offer can only be associated if the same user owns both of them. A diagram can be very helpful. Thanks.

Answer 1

Assuming a user can own own zones and offers independently from whether zones and offers are actually connected, you can do something like this:

This is a classic problem of diamond-shaped dependency where the diamond bottom must reference the same diamond top for both diamond sides, which is ensured by using identifying relationships so the top PK is propagated down both sides and then merged at the bottom (note FK1 and FK2 in front of ZONE_OFFER.USER_ID).

You can always add more (surrogate?) keys to this model if there are external reasons to do so, but this basic structure must exist in some shape or form to ensure only zones and offers of the same user can be connected.

Answer 2

Here is your database model:

Tbl_Zone
    id_Zone not null
    id_User not null
    zoneDescription not null
    ....

Tbl_Offer
    id_Offer not mull
    id_User not null
    offerDescription not null
    ....

Tbl_ZoneOffer
    id_ZoneOffer
    id_Zone not null
    id_Offer not null

Back to your user's rule, this will be part of your business logic, where tables will be filtered before being displayed:

SELECT * FROM Tbl_Zone WHERE id_User = myUserIdentifier
SELECT * FROM Tbl_Offer WHERE id_User = myUserIdentifier

and, for the list of offers available per zone:

SELECT zoneDescription, offerDescription FROM Tbl_ZoneOffer 
    INNER JOIN Tbl_Zone on Tbl_ZoneOffer.id_Zone = Tbl_Zone.id_Zone 
    INNER JOIN Tbl_Offer on Tbl_ZoneOffer.id_Offer = Tbl_Offer.id_Offer
    WHERE Tbl_Zone.id_User = myUserIdentifier