Solved

inventory with product expiration dates

Posted on 2008-10-14
2
324 Views
Last Modified: 2012-06-27
I'd like some suggestions on how to handle an online shopping cart system where the inventory being sold will consist of products with expiration dates.  I could have 10 cases of product X, but 5 cases expire in 09/2009 and the other 5 expire in 04/2010.  I guess this is a variation of FIFO?

We'll need to run reports of expiring items so they could be discounted or whatever to get them off the shelves.

Any table structure (and other) suggestions would be greatly appreciated!
0
Comment
Question by:marcodiana
2 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
Comment Utility
Product
* ProductID -- PK
* Description
* Any other things about the product itself

ProductLot
* ProductLotID -- PK
* ProductID  -- FK to Product
* OriginalQtyInLot -- You might omit this ... but it could be useful information
* CurrentQtyInLot -- this must be decremented as you sell stuff off
* ExpirationDate
0
 

Author Closing Comment

by:marcodiana
Comment Utility
THANK YOU!  I knew I was over-thinking this and needed to have outside eyes point me in the right direction.  Based on this structure, I can accomplish all of the other pieces I'd need, and accomplish them rather easily.  I couldn't see the forest for the trees.  :-)  Thanks again.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now