Designing many to many relationship between three entities
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.
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.
- How should international geographical addresses be stored in a relational database?
- database - flattened out vs. normalized
- Ensure uniqueness in join table with start/end dates
- Does a SELECT query always return rows in the same order? Table with clustered index
- Reuse Entity in Entity Framework Core
- How to reduce the required storage space for large indexes and large PostgreSQL tables to improve and ensure performance?
- Is better use an empty value as a '' or as NULL?
- "Dynamic" tables in SQL?
- How to programmatically check if row is deletable?
- How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
- How do I structure data common to all users in Cloud Firestore?
- Table constraint for range and null values
- Calculating and saving space in PostgreSQL
- How would you set up a database to handle comments for a blogging site?
- How do multi user applications use databases?
- Why is read repair not sufficient in making dynamo-style database linearizable?
- Get table and column "owning" a sequence
- Can you define a foreign key in a table from which the table with the primary key is derived?
- How entity framework works for large number of records?
- Do pizza toppings exist on their own in 3NF or does the column 'toppings' fail 2NF because it does rely on the pizza in practical reality?
- How to generate a diagram of a very large database schema (SQL Server)
- Custom cascade delete stored procedure for temporal tables in SQL Server
- Database Management: Where to store old data outside the database
- Saving barcode number in database that is generated with the logic
- Generalizing work orders database table design
- Removing a table without a unique index / constraint
- Dropping column in Postgres on a large dataset
- Cannot add or change a record because a related record is required in table 'COMPANY'
- Consolidating tables with one-to-one relationships
- How do I relate requesters of different type?