In my e-commerce application I would like that buyers and sellers interact via a messaging system to purchase products.

The process consists of:

  1. The buyer inquiring for a product
  2. The seller responds with a quote
  3. The buyer can negotiate
  4. The seller can create / modify the quote based on negotiations
  5. The buyer accepts quote which is then transformed into an order
  6. The seller sends an invoice to the buyer

It's also possible that a buyer immediately starts an order when he accepts the price listed for the product.

I'm a bit clueless as how to model this in a database. Do I create a table for Enquiries, for Quotes and for Orders? Can I use a status table to maintain the type of object? But with a table I then need to keep versions of it for every change that occurs I think? And then how do I link it to a message?

It may seem a lot of questions, but I simply don't know how to tackle this.

The following diagram is a part of my database scheme so that you have an idea of how my it is designed for the moment:

enter image description here

Update - Add image referred to in comment enter image description here

  • Let there be 1:N Quotes. Each Quote gets a new row (here's your versioning system). Quote is then 1:1 with an Order. Now you can have multiple Quotes per Enquiry. Once the Customer accepts the Quote, generate Order & Invoice. You could use the same table for all 3, but what if you have different fields on each? Probably best to defeat that problem before it inevitably happens and just use multiple tables. – Jacob H Jun 21 at 16:32
  • Sorry for the late reply, but thanks for your answer. This indeed fixes my versioning system. I've updated my design and added an image of it in my question. Cannot link from here to that image apparently. – JeremyVm Aug 5 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.