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.