to describe my problem, I'll provide a simple example:
My database has three tables:
[positions] - position_id INT - position VARCHAR [employees] - worker_id INT - position_id INT - FK - title VARCHAR - birth_date DATE [automobiles] - vehicle_id INT - model VARCHAR - year VARCHAR - color VARCHAR
However , I have to connect one vehicle with one worker whose position in the organization is "Driver", and just for the reason that situation.
I attempted to make use of inheritance and make another table known as "Driver" getting a ForeignKey connected with one worker (1-1 relationship), however i could not have great results because within the programming stage I'm going to by hand verify when the selected position id (within the HTML choose element) may be the id from the "Driver". In my opinion that's a bad programming practice.
To conclude, I must determine if you will find different ways to get this done without prejudice the relational database or even the programming.
Thanks ahead of time! And sorry for that bad british, it isn't initially my language. I think you'll can understand.
This can be a business rule that - "Only worker with position = drive could be connected having a vehicle". Business rules are usually implemented within the programming and it is not necessarily a bad practice. Programming is perfect for writing business logic. Generally you're going to get a lot of such instance which can't be implemented at database level while developing any application.
If however you'll still wish to control this at DB level you'll be able to use trigger and appearance this validation at place/update level.
Regrettably relational databases just don't make good hierarchical object stores. You may think about using some type of Object-Relation Model to fake it, but you are right: it isn't good practise. Possibly think about a purpose-built object datastore rather than a conventional RDBMS.
You will find various ways to get it done with assorted tradeoffs. Scott Ambler includes a great page listing the options with diagrams.
The easiest method to do that will probably possess a table
EmployeeVehicles that connects employees to automobiles. Yes, what this means is the application (or possibly a trigger or saved procedure) would need to make sure that just the specific kinds of
Employee would really possess a record in
EmployeeVehicles, but individuals are usually the the best places to store your company logic. The database can there be to keep data within the most stabilized possible way, to not keep an eye on business-specific rules. So far as it must know, some employees (..*) may have automobiles (1..*, or possibly 1..1).