CODEWITHSUNDEEP
postgresqldatabase-designmapping
Almas Abdrazak
Almas Abdrazak

Reputation: 3632

How to map tables without FK

I have these tables:

Connections: one carWash to many orders, one order to one orderHistory, one carWash to many services, one user to many orders

The question is: when user create order he choose some services that are provided by car_wash for example (wash_car_body = 20$, wash_wheels = 10$ ) from services table and when user wants to see order history I want to show all chosen services to user, how to do it better?

My services script: 

create table services(
  id SERIAL not null,
  car_wash_id int not null, 
  name text not null,
  price double precision not null, 
  car_body text,

  CONSTRAINT "gmoika_service_company_id_fKey" FOREIGN KEY (car_wash_id)
      REFERENCES car_wash (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Car Wash Script: 

create table services(
      id SERIAL not null,
      name text not null)

For example 

insert into services (car_wash_id, price , name car_body) values (1,20,wheels_wash,sedan)

And when user create order to car_wash with id = "id" i use the following script to give him all services ,

select * from services s where s.car_wash_id = "id".

Then user choose services. And i want to save choosen services into order history. OrderHistory script 

CREATE TABLE order_history(
id SERIAL not null,
order_id int,
wash_price double precision,
car_body text,
box_number int,
car_wash_name text,
currency text,
time timestamp with time zone NOT NULL,
status text,
CONSTRAINT "gmoika_wash_history_id_fKey" FOREIGN KEY (order_id)
      REFERENCES order (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Upvotes: 0

Views: 42

Answers (1)

Martin Campbell
Martin Campbell

Reputation: 1798

You'd need to add another table, e.g. orderServiceHistory which will be a mapping of order_id to service_id. Both of these will be foreign keys to the order & services tables. You could then use this to store which services were taken from a historic car wash order.

However, I'd recommend you think about your schema a bit more. Few things to consider off the top of my head:

  1. Why the mix of plural and singular form (e.g. order vs services)
  2. You haven't defined your primary keys
  3. What is the purpose of the one to one mapping between order & orderHistory?
  4. How would you handle when the price of a service changes?
  5. How would the removal/inactivation of services & car washes etc be handled?

Upvotes: 2

Related Questions