E-Commerce Database Design Issues

Question

Let's say I have 2 “things” that I can sell: service and product . Each of them is represented by a table in which their unique attributes are recorded (for example, a service can have a speed per hour, while a product can have a unit price, etc.).

Each time a sale is made, suppose I need to capture accurate sales data regardless of whether the service or product is for sale. How to do it?

APPROACH 1:

• I create one table to store the details of each sale.
• I create two mapping tables to connect a product or service to a sales table.

So something like this:

 TABLE: product - product_id (PK) TABLE: service - service_id (PK) TABLE: sale - sale_id (PK) TABLE: product_sale - product_id (FK) - sale_id (FK) TABLE: service_sale - service_id (FK) - sale_id (FK) 

APPROACH 2:

Skip mapping tables and simply have separate tables for recording sales for products and services.

 TABLE: product - product_id (PK) TABLE: service - service_id (PK) TABLE: product_sale - product_sale_id (PK) - product_id (FK) TABLE: service_sale - service_sale_id (PK) - service_id (FK) 

I believe approach 1 would be better, since I would need to generate reports such as:

• Total sales for a certain period
• Total sales per person.
• and etc.

Answer 1

Your approach 1 is more favorable than approach 2 for the reasons you have indicated. The ability to collect data on all sales of any type and have several items in one sale. Approach 2 assumes that products and services are sold only one at a time and are not combined together. But I have to wonder why you want to separate products and services in the first place? (It depends on the industry depending on your needs). Most e-commerce and small business accounting systems use a simpler model with a single product table.

So here is my official answer to your question using one product table:

• Create a table [product] to have PricePerUnit and UnitOfMeasure (there may be hours, there may be each, there may be pairs, there may be a book rate for standard services). This can adequately describe the price difference between products / services for most small companies.
• Now in your [product_sale] table (for example, "InvoiceLineItem" in my lingo), add a note box. In this area you can write in detail about the sold product / service. In the case of a service, you can enter some notes so that the client can be referred to as “Completed service X, adjusted widget B and polished C-tuning Handle”.

So, you have three tables in this simple example:

• [products] - contains the basic data or your "catalog" of products and services, including their assessment for a specific unit of measure.
• [invoices] - contains information about your sale or the title of the invoice, including the date, transaction ID, possibly the seller, etc.
• [invoicelineitems] - Associates invoices with products with two many-to-many foreign keys.

This is like most off-the-shelf e-commerce systems and small business accounting systems.

You can definitely go more complicated if you need super-specific tracking, but you need to justify the costs and complexity by explaining why the unit price of the circuit will not work for your use case. (car dealers are an example where services are tracked completely differently than usual with services / products in separate tables linked to items that represent one “problem” or “complaint” about your car. Each item has both a product component and service component.)

UPDATE: It looks like I was not clear enough in my original answer, as @Doug noted. When you create an invoice for the sale, the current PricePerUnit is copied to the InvoiceLineItem record along with the QuantitySold field and any other data that is important for snapshot of the product master data if you need to cancel the transaction. This ensures that the account will always maintain the same amount, even if the price of the product changes in the future. It also allows you to cancel the invoice if the customer returns something, and make sure that the customer receives the same amount as paid, regardless of the current price.

Answer 2

Actually, I would do it differently:

 TABLE: salable - salable_id (PK) - price (per unit) - unit ("hour", "kilogram", "part", "unit") - description TABLE: product - salable_id (FK) - manufacturer - units_in_stock TABLE: service - salable_id (FK) - provider TABLE: sale - sale_id (PK) - salable_id (FK) - units - total 

The bottom line is that “commodity” abstracts things that are common to services and products. This may not be the ideal solution in your case, but it is a viable design that others have not mentioned yet.

ADD: to ensure accurate records, the most respected commercial / financial systems will take a snapshot of each product / service record for each position of each sale. Since we must take snapshots of our product records for each sale in any case , a simpler and simpler approach is to have all products sold share a set of basic fields.

Answer 3

Approach 1 is best for your goals. And it is also better suited for a middleware layer created on top of your database level — you can easily place an ORM solution on top of an existing database structure modeled with the first approach.

Answer 4

I have done this more than once:

desktop product: - product_id (PK) - description - product_type_id (FK)

billing table:

• invoice_id
• client_id

invoice_line table: - invoice_id - Product code - product_type_id - price

table product_type - product_type_id - description

So, on your last table, set two itens: 1 - "product", 2 - "service", Products would be created like this: "1", "banana", "1" (this is a product); Services will be created in such a way as "2", "Change of oil", "2" (this is a service);

Now when creating invoices, make sure that you pass product_type_id for the selected product to the invoice_line table. This will give you more performance when querying data such as "SELECT * from invoce_line WHERE product_type_id = 2" to get all the sales data. (1 for products).

So, you save all your account data in a unique table; you can get, for example, all client services through a simple connection request, for example (client_id will be 10):

 Select * from invoice_line join invoice on invoice_line.invoice_id = invoice.invoice_id and client_id = 10 where product_type_id = 2 

That would do the trick for almost everything. I have 1000 people working on something like this and to this day no shortcomings.

Hope this helps.

Answer 5

The first approach is favorable if you are trying to normalize your dB. In this case, you can reuse the sale entity attributes with sale_id = xx for different products. In the second, you will need to duplicate the sale attributes if you are selling more than one product / service per sale (sale transaction).

A date / date query can be resolved using the index strategy / sections. If you know that your data will grow rapidly ... so that will be another question regarding the DW project.

Hope this helps.

Answer 6

At the time of writing this answer, I believe the answers are not visible:

What happens when the price of something changes?

I talk about this because you specifically mention reporting.

Are all previous sales now showing a new price? Are all services paid at the same rate (or do some customers get a discount?)

I would say that your concept of the domain "Sale" should contain "Sold items", which are objects of pure value.

It may not matter - but you have to decide such things. This is why domain models get a lot of hand-waving.

So, I propose option 1, but taking into account the fact that in the table of sales of products / services, one should refer to the sold_product and sold_service, and not to the product / service "origin".