CODEWITHSUNDEEP
databaseinheritancerelational
Philipe
Philipe

Reputation: 572

Correct way to model relational database

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.

Upvotes: 1

Views: 227

Answers (4)

Andy Dent
Andy Dent

Reputation: 17969

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

Upvotes: 1

Ramesh Soni
Ramesh Soni

Reputation: 16077

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.

Upvotes: 5

drharris
drharris

Reputation: 11214

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).

Upvotes: 0

S..
S..

Reputation: 341

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.

Upvotes: 2

Related Questions