Hi there,

My wife is starting as a beautician and I want to make her a kind of CMS/CRM. I'm designing the database which is needed for this application, and I was wondering about the following:

A client can buy cosmetic treatments as well as cosmetic products. In some cases the cosmetic products are used in treatments.

I was thinking about this

CLIENTS
client_id | client_name | etc.

TREATMENTS
treatment_id | treatment_name | treatment_base_price |etc.

PRODUCTS
product_id | product_name | product_base_price | etc.

CLIENTS_TREATMENTS_HISTORY
client_id(FK) | treatment_id(FK) | date | price | etc.

CLIENTS_PRODUCTS_HISTORY
client_id(FK) | product_id(FK) | date | price | etc.


Or is it better to 'merge' the treatments and products table and have an extra column called 'type(enum)' or something like that. Type is "treatment" or "product". In the database it's almost the same, but in real life a product differs from a treatment (like a massage).

Can somebody help me with this? Thnx.

What questions would you like the database to answer?

It sounds like there are at least two discrete questions with regard to products:

1. What products were sold to a client as a retail transaction.
2. What products have ever been used on a client as part of a treatment.

You have retail sales covered in CLIENTS_PRODUCTS_HISTORY. I would add a TreatmentProduct table with treatment_id and product_id if you need to know about products used during a treatment.

Not that it matters a great deal, but it's also considered good practice to name your tables singular and drop the history table unless it's a slowly changing dimension. Makes no difference really, but it might help quickly convey your table structure if you need more help in the future. I'd go with:

Client
Treatment
Product
TreatmentProduct
ClientTreatment
ClientProduct

ok, thank you.

For example:

Client A "buys" treatment A and after the treatment he/she also buys product X.

Then, a week later: client A comes to the store again but buys no treatment, only a refill of product X.

How can I cover that in the designs above?

(Many thanks in advance, i'm kindly new to db design...)

Do you need to make a distinction between an initial sale of a product vs. a refill?

In your scenario, you would have one record in ClientTreatment and two records in ClientProduct. It's as simple as treatments going in ClientTreatment and any straight retail sales going in ClientProduct, even if they happen during the same visit.

Accounting for refills may add considerable complexity depending on what you want to track.

Sorry, bad example with the refill. I meant product Y.

Yes, cool. And I have 1 record in TreatmentProduct where treatment A is linked to product X.

Just to clarify...

You would only record a sale of product X if the client purchased it outside of the context of a treatment. In other words, the container of product purchased by the client would not be the same container used during a treatment.

Yes, I now understand your concept. Many thanks!