Okay I have a table like so.

ID   Productname  Quantity          Image       QuantityOut  LastDateReserved

1    productOne          5     'image.jpg'                 3       2013-03-15
2    productTwo          1     'product.jpg'               2       2013-03-02

I have a page that checks for the last part of an event date + 5 days buffer. Now here is where I get really confused. Say for instance I want to check out only 3 of productOne, how would I make that work with this table schema? Do I need to add more fields or tables? The way I have it now is that my LastDateReserved would be overwritten with a new date. Thus erasing the old date and not being accurate on when the earliest date actually is. What would be the proper table structure here?

If my question needs to be reworded, please let me know and I will change it.

  • Each QuantityOut would have it's own LastDateReserved is what you are trying to achieve? – Kevin DiTraglia Mar 19 '13 at 21:28
  • Yeah, that is exactly right. Would I make a new table? – wowzuzz Mar 19 '13 at 21:28

Typically the way you would do this is using another reference table to hold the product ID with a date reserved. Something like this:

ProductID    DateReserved
1            2013-03-15
1            2013-02-15
2            2013-03-02

Then you could join this table to the table you currently have to get the x most recent. Your SQL could look something like this:

Select top 3 * from YourTable 
Inner Join ReferenceTable on YourTable.ID = ReferenceTable.ProductID
Order by DateReserved DESC
  • I'll give this a shot. – wowzuzz Mar 19 '13 at 21:37
  • Somehow I think the QuantityReserved would go nicely into that table. – Dan Bracuk Mar 19 '13 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy