2

I have a table of products. The products can be rented. I have another table validity_periods. Here is the structure of tables.

http://sqlfiddle.com/#!2/c97e3

validity_periods

id  description
1   7 Days
2   14 Days
3   21 Days
4   1 Month
products

id              : 1
title           : Car
description     : None
validaity_id    : 1
category_id     : 1
rent_count      : 0
current_status  : available
start_date      : 0000-00-00 00:00:00
end_date        : 0000-00-00 00:00:00

Now i am doing the folliwing.

When user creates this product i provide a dropdown to select maximum validaity period for this product.
The drop down contains validity periods. And save the validity_id with product.
When someone rents this product i update the current_status from available to rented.
Takes the id find the current date and generate start_date and end_date and update the product.
Here i need two things
First : is there a simple solution that insted of doing a lot of work i simply do something to generate start_date and end_date? i can change the schema of validity_periods if required.
Second : When i display the product i display the status and the remaining days(after product will be available again to be rented).
So how can i achieve these two tasks.

Let suppose the product Car has been rented and now

id              : 1
title           : Car
description     : None
validaity_id    : 1
category_id     : 1
rent_count      : 1
current_status  : rented
start_date      : 2012-12-10 01:30:00
end_date        : 2012-12-17 06:50:00

Now i need to display this information.

Item        : Car
Status      : Rented
Rent Count  : 1
Remaining   : 3 Days 23 Hours
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
Muhammad Raheel
  • 19,823
  • 7
  • 67
  • 103
  • Do you need to calculate how many days are left for that item to be available? – juanreyesv Dec 10 '12 at 07:17
  • yes indeed also i am generating other queries to generate start and end date using mysql now() i want a simple alternative solution – Muhammad Raheel Dec 10 '12 at 07:18
  • Use Mysql timediff function. Check out [this post](http://stackoverflow.com/questions/5279079/mysql-convert-timediff-output-to-day-hour-minute-second-format) it will help you – juanreyesv Dec 10 '12 at 07:47

1 Answers1

1

It is better for you case to have a many to many relation between the Products table and the Periods table. Something like:

Products:

  • Id,
  • Name,
  • CategoryId,
  • Status InRent, Avialable, Not available, ...

Periods:

  • Id,
  • StartDate,
  • EndDate

ProductPeriods:

  • Id
  • ProductId
  • PeriodId
  • PeriodStatus (Cancelled, Ended, ...)

This design will make your queries a lot easier. For instance you can get the list of products that are rented now and number of rent counts like so:

| PRODUCTNAME | STATUSNAME | RENTCOUNT |
----------------------------------------
|        Bike |     Rented |         1 |
|         Car |     Rented |         4 |

You can however get for each product the periods that it is rented and the remaining date time for this rent.

SQL Fiddle Demo

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • thanks for the schema but how can i allow user while creating product to select validity periods? because this funtionality is necessary in my case. – Muhammad Raheel Dec 10 '12 at 07:49
  • @raheelshan - The validity periods will be stored in the `periods` table, as `StartDate` to `EndDate`. For example, In your application you can display two date time pickers one the start date and another one for the end date, you read these two dates and insert them in the periods table. – Mahmoud Gamal Dec 10 '12 at 07:51
  • Well thak you thats indeed great solution i was short sighted looking only a dropdown. – Muhammad Raheel Dec 10 '12 at 07:53
  • @raheelshan - Try it and if there is anything goes wrong, don't hesitate to ask again or update your question. Good luck :) – Mahmoud Gamal Dec 10 '12 at 07:56