Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

gitwithravish's avatar

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.

0 likes
0 replies

Please or to participate in this conversation.