Hi all! I am trying to solve this problem for a while but i couldn't still solve it.
Let there be a company which has many service centers. I created a table which contains information about them:
Table "service_centers"
-----------------------
id (primary key)
name
address
Each service center makes some works, one can change tires on car, the other can repair engines, etc... I created a table which contains service types:
Table "service_types"
---------------------
id (PK)
name
... and bound it to "service_centers" table, to determine what kind of services each service center supplies:
Table "SC_ST"
-------------
id_service_center (references to service_centers.id)
id_service_types (references to service_types.id)
There are works related to each service type (for example, on "tire" service type the following works can be done: tire replacement, air pumping. On "engine" service type there could be "engine repairing", "oil changing"). So i created a table which contains all work types:
Table work_types
----------------
id (PK)
name
... and bound it to "service_types" table
Table ST_WT
-----------
id_service_type (references to service_types.id)
id_work_type (references to work_types.id)
Now i create a table which contains info on work done on all centers:
Table works
-----------
id (PK)
car_number
service_center_id
work_type_id
Seems like it's ok but i can't restrict entering wrong work types, for example:
Table "service_centers"
-----------------------
id name address
-------------------------------------------------
1 servcen1 A st, 1
2 servcen2 B st, 2
3 servcen3 C st, 3
=========================================================
Table service_types
-------------------
id name
------------------------------------
1 tires
2 engine
===========================================================
Table SC_ST
-----------
service_center_id service_type_id
------------------------------------------------------------
1 1
2 2
3 1
3 2
==============================================================
Table work_types
----------------
id name
----------------------------------------------------------
1 Tire replacement
2 Air pumping
3 Engine repairing
4 Oil changing
================================================================
Table ST_WT
-----------
service_type_id work_type_id
------------------------------------------------------------
1 1
1 2
2 3
2 4
===================================================================
Table works
-----------
id car_number service_center_id work_type_id
--------------------------------------------------------------------------
1 123-4567 1` 1
1 321-6547 1 3 <-- Incorrect! Engine repairing
cannot be done on a service
center which provides only
"tire" service
And THE QUESTION IS:
How can i avoid this kind of problem? I tried to solve it by foreign keys, but seems like it's impossible.
Thank you.