Defining foreign key of a table with composite primary key
I am working on Hotel Booking Portal's user dashboard page. The dashboard allows a property owner to define his property in detail. A property can be assigned to more users with specific access rights, For example, user1 can access part A of the dashboard and user B can access part B of the dashboard.
I am having some trouble creating database design for this.
The following table defines the allocation of properties and users.
`property_user_master`
- id_property
- id_user
Following table defines the access rights for a particular user of a particular property
`user_access_rights`
- id_property
- id_user
- id_access_right
Now here, the combination of propertyID and userID has to be referenced from the 1st table.
How to define a foreign key that references the primary key of table property_user_master, which is a composite key.
Please or to participate in this conversation.