What is right way to build DB correctly?

Question

I have following atributes

Auto Mechanic(id,name,mech_type,work_type,work_price)

For example:(1,John,body repair,spoiler replacement,100$)

I divided it by the tables like this

1. Car Mechanic (mech_id,mech_name)
2. Mechqual(mechqual_id,mech_id,mechtype_id)
3. mechtype(mechtype_id,mechtype_name)
4. Work(work_id,car_id,mech_id,work_type,work_price) So mech-mechqual is M-M and mechqual-worktype 1-M.

But I think that`s not good, because you can write work_type that is not linked with mechtype of mechanic.

For example: mechqaul - brake technicians work_type - car painting. What I should change? How to avoid incorrect fill of DB?

Answer 1

I've changed the names for readability. I think the situation is as follows:

table(columns)                                    | sample content
--------------------------------------------------+------------------------------------------------------
worktype (worktype_id, name)`                     | 1/'body repair' , 2/'car painting'
workpart (workpart_id, worktype_id, name)         | 100/1/'spoiler replacement', 200/2/'partial painting'
mechanic (mechanic_id, name)                      | 123/'John'
ability  (mechanic_id, worktype_id)               | 123/1, 123/2
workdone (mechanic_id, workpart_id, price, car_id | 123/100/90$/4444

and you are worried, because with this model the DBMS cannot prevent workdones on workparts the mechanic has no ability for.

This is due to the fact that this datamodel is purely based on single technical IDs which has this drawback not to guarantee consistency in hierarchies.

If you'd use composite keys instead:

table(columns)                                                 | sample content
---------------------------------------------------------------+--------------------------------------------------
worktype (worktype_no, name)`                                  | 1/'body repair' , 2/'car painting'
workpart (worktype_no, workpart_no, name)                      | 1/1/'spoiler replacement', 2/1/'partial painting'
mechanic (mechanic_no, name)                                   | 123/'John'
ability  (mechanic_no, worktype_no)                            | 123/1, 123/2
workdone (mechanic_no, worktype_no, workpart_no, price, car_no | 123/1/1/90$/4444

The workpart's primary key now is worktype_no + workpart_no. So a workpart_no without a worktype_no has no meaning. Only the combination tells you which workpart. Accordingly the workdone table contains both worktype_no and workpart_no in order to say what this work was about. And now you can have a constraint to the ability table. Problem solved.

You also mention, that this is about clients. Clients have cars and mechanics. Again the problem that a mechanic should only repair cars that belong to their client. And the solution is the same: A mechanic would have PK client_no + machanic_no and a mechanic_no alone would no longer have a meaning. Same for cars. And once more the workdone table would contain all the information that is needed to build proper foreign key constraints that prevent from inconsistencies.

Conclusion: It is not wrong to build a database on single technical IDs. This method is easy to use and widespread. But it does have the drawback of not guaranteeing consistancy over multiple levels (hierarchie). This is why I usually prefer to build a database on composite keys, although (or maybe because) it takes a bit longer to decide for the correct keys and build the database.