Relationship between orders, customers and products?

Question

I am trying to construct an e-commerce database but I don't understand relationship between orders, products and customers. There are lots of database examples but they are too complex. Is there a simpler explanation or an example about possible tables and relationships.

Thanks.

Answer 1

This is the simplest form if customers can have more than one order and orders can be for more than one product:

The ORDER table has information about the date and status of the order.

The ORDER ITEM table has information about the quantity of the product ordered.

The only way to get simpler than this would be if you have a business rule restriction that says "I don't keep track of my customers from one order to the next" or "each order is for exactly one item". If you did have these (unrealistically simplistic?) rules, then you could reduce the number of tables in your model.

Note that this very simple model starts to get much more complicated very quickly the more flexible and realistic you want it to be. Adding in pricing, payment methods, payments, or inventory, for example, would all add a lot of complexity.

Answer 2

I had a "hand rolled" e-commerce system. The business had an internal web+mysql server (typical ubuntu LAMP). While the design of the database structure was not well optimized, it worked well.

The tables we used were: order_main order_details order_payment

In here, a lot of "linked" data from the above ideal model was simply replicated. The website front end was simple and dumb. Customers had to re-enter their data with every order.

In the above case, the MySql was updated via php scripts run by cron or by user interaction. The files of the website were updated from a master spreadsheet which would be processed to generate static HTML files. So the flatfile could be considered the master or originator of the item data. The website generated transient customer data, which was eventually fed into Quickbooks, which ran the entirety of the business process.

Orders would reside on the webserver for a minute until the internal LAMP server pulled them off. This method worked for about 7 years (eventually abandoned when we went to Magento).

Later on, item level was added and an entire UI built around creating the website from a MySql database rather than a spreadsheet flat file. This took me about a year to code and we used it for about 2 years while Magento improved enough to put into production. Now, Magento run the business process and exporting to Quickbooks is an afterthought (with regard to customer experience) and QB is used solely for accounting. I feel much better not having to rely upon QB for my day-to-day income.