One to one and one to many together how to design?

I have a car table and driver table. Each car can be driven by some drivers but one of them must be active driver and each dri开发者_StackOverflowver can drive one and only one car. I need to query for active driver and I need to find the car a driver can drive even he is active or not. How should I design the tables and constratints ? I put FK to each other but it does not feel correct.

This is a tough one to manage using database constraints. You can put a foreign key column in your CAR table pointing at DRIVER and a foreign key in DRIVER pointing at CAR but that doesn't guarantee that each car and driver point at each other.

Original Suggestion:

The only way to enforce this using database constraints only (i.e. no procedural code) is to create a third table like so:

DRIVER_ASSIGNMENT
( car_id int not null
, driver_id int not null 
, start_time datetime not null
, end_time datetime not null
, primary key (car_id, driver_id, start_time)
, unique key (car_id, start_time)
, unique key (driver_id, start_time)
)

This way each car can have only one driver and each driver can have only one car (at a time). If you really want to get fancy you can also define check constraints that ensure that no two assignments for the same driver overlap in time.

Edit: In this schema, some cars and some drivers may be unassigned, but none can be over-assigned. Edit 2: Given new comment from OP regarding need for multiple assignments over time.

Edit 3: Simplification/Separation of Concerns:

Paul commented that my original suggestion was complex due to mixing history with the current assignment. I agree with this. It was something that was nagging at me actually after I posted the solution. It seems to me the best approach is one that meets the OP's requirement to keep one car and one driver mapped together at any one time - using database constraints only and without resorting to procedural business rule code to enforce the cardinality, while at the same time providing for the requirement to track who's been driving which car and when.

Therefore I would revise the design to use two separate tables like so:

DRIVER_ASSIGNMENT
( car_id int not null
, driver_id int not null 
, start_time datetime not null default GETDATE
, primary key (car_id, driver_id)
, unique key (car_id)
, unique key (driver_id)
)

DRIVER_ASSIGNMENT_HISTORY
( car_id int not null
, driver_id int not null 
, start_time datetime not null
, end_date datetime not null  -- This is optional. It is nice to have but not necessary.
, primary key (car_id, driver_id, start_time)
)

With this schema drivers and cars can be paired with each other one at a time but the history of these pairings is tracked. The DRIVER_ASSIGNMENT table enforces the cardinality and the DRIVER_ASSIGNMENT_HISTORY table provides the ability to see who was driving what and when. The only procedural code you would have to write would be the code that takes each insert or update in DRIVER_ASSIGNMENT and creates an insert into DRIVER_ASSIGNMENT_HISTORY. This could be created as a database trigger, in which case the application still wouldn't need procedural code and everything could be wrapped in a tidy transaction to keep everything consistent.

To manage these requirements I would suggest using a junction table in this manner:

Driver > Table
    Id  - pkey
    ... other fields

CarDriver > Junction Table
    DriverId - part 1 composite pkey, foreign key to Driver.Id
    CarId - part 2 composite pkey, foreign key to Car.Id

Car > Table
    Id - pkey
    ActiveDriverId - foreign key to Driver.Id
    ... other fields

The CurrentDriverId field in the Car table would indicate the current driver while the CarDriver junction table would show which drivers can drive which cars. Notice that it takes very few fields to maintain the required information and state.