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)

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)

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)