1. Forum
  2. Usenet
  3. COMP.DATABASES.MYSQL

  • How to store product's warranty

    From ^Bart@21:1/5 to All on Fri Feb 7 22:44:44 2020
    Hi Guys, I'm thinking about the best way to store warranties about
    products and I thought to create:

    warrantytypes
    -------------------------------------
    warrantytype_id name
    1 12 Months
    2 24 Months
    3 6 Months

    products
    -----------------------------------
    product_id name warranty start warrantytype_id extendedwarranty
    1 Samsung S2 01/01/2020 1 3
    2 Samsung S4 01/02/2020 1 3
    2 Samsung S3 30/12/2019 2 3

    Extendedwarranty is linked to warrantytype_id, now I should understand
    if it could be a good idea to add a field in products table named "expiredwarrantyday" to set if a product is out of warranty.

    Why I thought to create a specific field about expiredwarrantyday?
    Because I think I could add a code to calculate every day if a product
    is out of warranty (expiredwarrantyday > today) and I could do a
    "simple" query to this column to understand in a fast way how many
    products are out of warranty!

    It could be a good idea also to set a count down about warranty (for
    example 10 days, one month, etc.) before the expired day but now I'd
    like to know what do you think about it!

    Do you think I could create another field named expiredwarranty (yes/no,
    0/1) to be more fast to calculate what products are out of warranty? I
    could run a script to calculate it every day...

    Regards.
    ^Bart

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From J.O. Aho@21:1/5 to All on Sat Feb 8 16:54:00 2020
    On 07/02/2020 22.44, ^Bart wrote:
    Hi Guys, I'm thinking about the best way to store warranties about
    products and I thought to create:

    warrantytypes
    -------------------------------------
    warrantytype_id name
    1        12 Months
    2        24 Months
    3        6 Months

    products
    -----------------------------------
    product_id name       warranty start warrantytype_id extendedwarranty 1       Samsung S2 01/01/2020     1             3 2       Samsung S4 01/02/2020     1                 3
    2       Samsung S3 30/12/2019     2               3

    If you only looking at the length, then just have a warranty length, not
    really need of a separate table for that.

    Extendedwarranty is linked to warrantytype_id, now I should understand
    if it could be a good idea to add a field in products table named "expiredwarrantyday" to set if a product is out of warranty.

    Depends on how often you need that values, you can always use TIMESTAMPADD(MONTH, warranty_length, warranty_start)
    , but of course if you need to make more advanced queries and need it
    indexed, then maybe better to store the value from start.



    Why I thought to create a specific field about expiredwarrantyday?
    Because I think I could add a code to calculate every day if a product
    is out of warranty (expiredwarrantyday > today) and I could do a
    "simple" query to this column to understand in a fast way how many
    products are out of warranty!

    It could be a good idea also to set a count down about warranty (for
    example 10 days, one month, etc.) before the expired day but now I'd
    like to know what do you think about it!

    That you show with timestampadd + datediff, no need to make unneeded
    table updates.

    Do you think I could create another field named expiredwarranty (yes/no,
    0/1) to be more fast to calculate what products are out of warranty? I
    could run a script to calculate it every day...

    A bit showing if a product still has warranty can be good, then you can
    exclude those already passed when you are looking for how many days it's
    left until the warranty expires.


    --

    //Aho

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From ^Bart@21:1/5 to All on Thu Mar 5 23:02:08 2020
    If you only looking at the length, then just have a warranty length, not really need of a separate table for that.

    I'd like to have a separate table about warranty time because I'd like
    to have a field for a standard warranty and another field for an
    extended warranty.

    Depends on how often you need that values, you can always use TIMESTAMPADD(MONTH, warranty_length, warranty_start)
    , but of course if you need to make more advanced queries and need it indexed, then maybe better to store the value from start.

    I read about TIMESTAMPADD but for example how could I manage something
    like what I wrote above?

    warranties
    --------------------------
    warranty_id month
    1 12
    2 6


    productwarranties
    --------------------
    productwarranty_id 1
    product_id_fk 20
    warrantystartdate 01/01/2020
    warranty_id 1
    warrantystopdate 01/01/2021
    warrantyextended 2
    warrantystopdate2 01/06/2021

    I wrote just what I'd like to have I know it's not right but I'd like to
    store every warranties and maybe I could have "x" extended warranties
    for a product not just two like what I wrote above!

    It's important to see the story of every warranty because a customer
    could have one or more than one extended warranty and it should be
    possible to show it!

    A factory would like to see if it gived to a customer more than one
    warranty and not to show just the complete lengh...

    I think I should have a warranty table linked in FK to the products and
    I should remove warranty field from the product's table!
    That you show with timestampadd + datediff, no need to make unneeded
    table updates.

    Timestampadd is very nice, I didn't know it and I didn't know also the
    feature datediff but in my case it's not useful... :\

    A bit showing if a product still has warranty can be good, then you can exclude those already passed when you are looking for how many days it's
    left until the warranty expires.

    Ok!

    Thanks for every your replies! :)

    ^Bart

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)