databasedatabase-designforeign-keysdatabase-relations

Resolve the database relationship cycle


In the database I have a model of kind: enter image description here

The customer can either rent a car or rent out a car. As you see Car has OwnerID - customer who owns a car but at the same time customer can also rent a car from another owner so that in Order table he appears as User.
So how can I change the model to avoid such cycles and is it even possible?


Solution

  • Consider identifying (storing in tables)

    .

    create table persons (
      -- I prefer "people". YMMV.
      person_id integer primary key,
      person_name varchar(25) not null
      -- Other columns go here.
    );
    
    create table cars (
      -- VIN might be a better choice.
      car_id integer primary key
      -- Other columns go here.
    );
    
    create table car_owners (
      -- Only car owners known to us can rent out a car.
      car_id integer not null
        references cars(car_id),
      owner_id integer not null
        references persons (person_id),
      primary key (car_id, owner_id)
      -- Other columns go here.
    );
    
    create table customers (
      -- Any person can rent a car. (But some persons are not customers.)
      customer_id integer primary key  
        references persons (person_id)
      -- Other columns go here.
    );
    
    create table rentals (
      customer_id integer not null
        references customers (customer_id),
      car_id integer not null,
      owner_id integer not null,
      primary key (customer_id, car_id, owner_id),
    
      -- Don't rent a car unless we know who the owner is.
      foreign key (car_id, owner_id)                  
        references car_owners (car_id, owner_id)
    
      -- Other columns go here.
    );