Correct way to model relational database

Question

Hi, to explain my problem, I'll give a simple example:

My database has three tables:

[positions]
 - position_id INT
 - position VARCHAR

[employees]
 - employee_id INT
 - position_id INT - FK
 - name VARCHAR
 - birth_date DATE

[vehicles]
 - vehicle_id INT
 - model VARCHAR
 - year VARCHAR
 - color VARCHAR

The problem is that I must associate one vehicle with one employee whose position in the company is "Driver", and only in that case.

I tried to use inheritance and create another table called "Driver" having a ForeignKey associated with one employee (1-1 relationship), but I couldn't make it work because in the programming stage I'll have to manually verify if the selected position id (in the HTML select element) is the id of the "Driver". I believe that is not a good programming practice.

In conclusion, I would like to know if there are other ways to do this without prejudice the relational database or the programming.

Thanks in advance! And sorry for the bad english, it's not my primary language. I hope you can understand.

Answer 1

Unfortunately relational databases just don't make good hierarchical object stores. You might consider using some kind of Object-Relation Model to fake it, but you're right: it's not good practise. Perhaps consider a purpose-built object datastore instead of a traditional RDBMS.

Answer 2

This is a business rule that - "Only employee with position = drive can be associated with a vehicle". Business rules are generally implemented in the programming and its not a bad practice. Programming is made for writing business logic. In general you will get tons of such instance which cannot be implemented at database level while developing any application.

However if you still want to control this at DB level then you can use trigger and check this validation at insert/update level.

Answer 3

The best way to do this is likely to have a table EmployeeVehicles that connects employees to vehicles. Yes, this means your application (or perhaps a trigger or stored procedure) would have to ensure that only the specific types of Employee would actually have a record in EmployeeVehicles, but those are typically the best places to store your business logic. The database is there to store data in the most normalized way possible, not to keep track of business-specific rules. As far as it needs to know, some employees (0..*) might have vehicles (1..*, or maybe 1..1).

Answer 4

There are a number of ways to do it with various tradeoffs. Scott Ambler has a great page listing the alternatives with diagrams.