In the database I have a model of kind:
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?
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.
);