Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2011
    Posts
    5

    Two tables to hold client sold history?

    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.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    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
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2011
    Posts
    5
    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...)

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2011
    Posts
    5
    Sorry, bad example with the refill. I meant product Y.

  6. #6
    Join Date
    Feb 2011
    Posts
    5
    Quote Originally Posted by Teddy View Post
    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.

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

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Feb 2011
    Posts
    5
    Yes, I now understand your concept. Many thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •