Search code examples
mysqldatabasedatabase-designconstraintsrelationship

Database design. Is trinary relationship the only way?

I have the following database schema that I have come up with:

Tables:

employees(id_pk, name)

employees_attend_at_service_desk(employee_id_pk_fk, service_desk_id_pk_fk not null) references employee and service_desk

service_desk(id_pk, name, shop_id_fk not null) references shop

works(employee_id_pk_fk,shop_id_pk_fk not null) references shop and employee

shop(id_pk, company_id_fk not null)

company(id_pk, name)

This database will be queried from a website and from a desktop app.

So take this example into account: shop 1 has service_desk 1 and 2 and shop 2 has service_desk 3. Employee 1 works only in shop 1.

I want to attribute a service_desk from shop 1 to employee 1, but I want to add some sort of constraint that won't allow me to add service_desk 3 for example because that service desk belongs to shop 2 and employee 1 works only in shop 1. Is a trinary relationship the only way to get what I require when considering database design?

What I have thought on doing is to query the DB to get all of shop 1's employees and then query the DB again to find out the shop's service_desks. Then I can distribute the employees of shop 1 into there respective service_desks.

Doing it this way means the database isn't very robust? Or is this design ok? And My app can take care of the constraints I need?

Thanks a lot in advance to all!

Solution

  • One option is to:

    1. add shop_id_pk_fk to employees_attend_at_service_desk
    2. Add a foreign key employees_attend_at_service_desk (shop_id_fk) referencing shop (id_pk_)
    3. Create a unique key in service_desk (id_pk, shop_id_fk. (Required for step 4).
    4. Add foreign key to employees_attend_at_service_desk (service_desk_id_pk_fk, shop_id_fk) referencing service_desk (id_pk, shop_id_fk) to ensure a valid shop is entered for the service desk.
    5. Add a foreign key employees_attend_at_service_desk (employee_id_pk_fk, shop_id_fk) referencing works (employee_id_pk_fk, shop_id_pk_fk)

    This will duplicate some data, but only one int column, and will mean that you can build the requisite constraints to ensure you have a valid employee on a service desk.

    You could also leave the validation up to your app. I don't think it is particularly bad practice either way.