CODEWITHSUNDEEP
sqldatabase-design
Daniel
Daniel

Reputation: 23

How should I design a sql schema for ecommerce listings and orders?

So I have 3 tables called Books, Listings and Orders that can be seen in the code sample. My problem is that I want to display listings on the frontend, except I want to omit the listings that already have an order attached to it. Should I add an "ordered" boolean to Listings? Is the foreign key from orders to listings appropriate?

Also, if I want to look at fulfilled orders, there is a problem if a listing is removed and it had a foreign key connected to a fulfilled order. How to I design that?

Table books{
  id int [pk, increment]
  title varchar
  isbn10 varchar
  amazon_used_price int
  edition varchar
  image varchar
}

Table listings{
  id int [pk,  increment]
  price int 
  date_listed datetime
  seller_email varchar
  book_id int [ref: > books.id]
}


Table orders{
  id int [pk, increment]
  completed boolean
  buyer_email varchar
  listing_id int [ref: > listings.id]
}

Upvotes: 0

Views: 55

Answers (1)

daShier
daShier

Reputation: 2116

You shouldn't add an ordered column as that will cause many more problems than it will fix: concurrency being one of them.

Instead you can select your listings and exclude ones that have orders:

    SELECT b.title, b.isbn10, b.amazon_used_price,b.edition, b.image, 
    l.id, l.price, l.date_listed, l.seller_email
    FROM listings l LEFT JOIN books b ON l.book_id = b.id
    LEFT JOIN orders o ON l.id = o.listing_id
    WHERE IS NULL o.id
    ORDER BY b.title, b.isbn10

I assumed a few things, including the order of the records you want, but you should get the idea from this.

Upvotes: 1

Related Questions