DB Design Questions for eCommerce

Question

Say I have 2 "things" I can sell: service and product. Each is represented with a table that captures their unique attributes (e.g. service might have a per hour rate while product may have a per unit price, etc.).

Each time a sale is made, assume that I need to capture the exact same data about the sale regardless of whether the service or product is sold. How do I model this?

APPROACH 1:

1. I create one table to hold the details of each sale
2. I create two "mapping" tables to connect the product or service with the sale 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 the mapping tables and just have separate tables to record 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 feel approach 1 would be better since I would need to generate reports like:

• Total sales for a given period
• Total sales per sales person
• etc.

Answer 1

Your Approach 1 is more favorable than approach 2 for the reasons you listed. Being able to collect up data across all sales of any type and having multiple items in one sale. Approach 2 suggests that products and services are only ever sold 1 at a time and not bundled together. But I have to wonder why you want to separate the products and services in the first place? (This would be industry specific depending on your needs). Most ecommerce systems and small business accounting systems use a simpler model with one products table.

So here's my official answer to your question using one products table:

1. Structure the [product] table to have PricePerUnit and UnitOfMeasure (could be hours, could be each, could be pairs, could be book rate for standard services). This can adequately describe the pricing model difference between products/services for most small companies.
2. Now in your [product_sale] table (aka "InvoiceLineItem" in my lingo), add a notes field. In this field you could write detail regarding the product/service sold. In the case of a service, you might enter some notes for the customer to reference like "Performed X service, adjusted B widget and polished the C tuning knob".

So you have three tables in this simple example:

• [products] - contains master data or your "catalog" of products and services including their pricing for some unit of measure.
• [invoices] - contains your sale information or invoice header including date, transaction id, maybe sales person, etc.
• [invoicelineitems] - connects invoices with products with two foreign keys in a many-to-many style.

This how most off-the-shelf ecommerce systems and small business accounting systems work.

You can definitely go more complex if you need super specific tracking, but you need to justify the costs and complexity by explaining why a price per unit schema won't work for your use case. (auto dealerships are an example where services are tracked totally differently than normal with services/products in separate tables both tied to line items which represent one "problem" or "complaint" about your car. Every line item has both a product component and a service component.)

UPDATE: It appears I wasn't clear enough in my original answer as @Doug pointed out. When you create an invoice for a sale, the current PricePerUnit is copied to the InvoiceLineItem record along with a QuantitySold field and any other data important to snapshot of the product master data in case you need to reverse the transaction. This ensures the invoice always maintains the same total, even if the products price changes in the future. It also allows you to reverse the invoice if a customer returns something and make sure the customer gets the same amount back as paid regardless of the current price.

Answer 2

At the time of writing this, answers are, I believe, overlooking one thing:

What happens when the price of something changes?

I bring this up because you specifically mention reporting.

Do all previous sales now show the new price? Do all services bill at the same rate (or do some customers get a discount?)

I would argue that your domain concept of a "Sale" needs to contain "Sold Items" that are purely value objects.

It may not matter - but you have to decide things like that. That's why domain models get a lot of arm-waving.

So I'm suggesting option 1, but with consideration that the product/service sale table should refer to a sold_product and a sold_service rather than the 'origin' product/service.

Answer 3

Actually, I'd 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 point being that "salable" 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 haven't mentioned yet.

ADDENDUM: To ensure accurate records, most respected sales/financial systems will take a snapshot of each product/service record for each line item of each sale. Since we ought to be taking snapshots of our product records for every sale anyway, the easier and more straightforward approach is for all sellable goods to share a set of core fields.

Answer 4

Approach 1 is best suited for your purposes. And it is also better suited for middleware layer built on top of your database layer - you can easily put ORM solution over existing database structure modeled with first approach

Answer 5

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

Quering by person/date range can be solve using index strategy/partitions. If you know that your data will grow fast... so it will be another question concerning DW project.

Hope it helps.

Answer 6

I've done it more than once this way:

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

table invoice:

• invoice_id
• client_id

table invoice_line: - invoice_id - product_id - 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" (it's a product); Services would be created like this "2", "Oil change", "2" (it's a service);

Now, when creating you invoices, make sure you pass the product_type_id for the selected product into the invoice_line table. This will get you more performance while querying data like "SELECT * from invoce_line WHERE product_type_id = 2" - to get all the data of sell services. (1 for products).

So, you keep all your invoice data inside a unique table; you can get, for instance, all the client's services through a simple join query like (client_id would 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

This would do the trick for almost everything. I got 1000+ people working over something like this and, till today, no flaws.

Hope it helps.