By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,564 Members | 834 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,564 IT Pros & Developers. It's quick & easy.

DB design using expiry date in products

P: n/a
Hi all,
What will be the best database design for an inventory control that
uses expiry date for its products.

Over a period of time, a particular product will have many expiry date
and that one particular expiry date could have multiple products

I am thinking of two approaches not knowing which one to go for.

First design is of one product having many expiry dates (a one to many
relationship between product table being on one side and expiry date
table being many side)

Second design is of many to many table for a product and expiry date.

Among one of my many requirements is to get the inventory balance of
products based on product description and expiry date.

Example:
My purchase is as follows
Description Qty ExpiryDate
Product A 100 15th Jan 2006
Product A 100 31st Mar 2006
Product A 100 30th Jun 2006
Product B 200 15th Jan 2006

My sales is as follows
Description Qty ExpiryDate
Product A 25 15th Jan 2006
Product A 50 31st Mar 2006
Product B 15 15th Jan 2006

My report should show me the balance as follows
Description Qty ExpiryDate
Product A 75 15th Jan 2006
Product A 50 31st Mar 2006
Product A 100 30th Jun 2006
Product B 185 15th Jan 2006

I thank you all in advance for your help

Nov 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Your 2nd suggestion is closer to what you need.

The central idea is that you are not storing 'product', but 'product
instances'. Each product instance probably belongs to a batch, which defines
when it was manufactured, and hence when it expires. Fields:
BatchID primary key
ProductID foreign key to Product.ProductID
Expiry date/time

The table that records your stock would therefore have fields:
BatchID the unqiue batch number (identifies the product and
expiry)
Quantity positive if moving into stock, negative when removed from
stock.
MoveDate the date when you added/removed this quantity of this batch
to/from stock.

The amount of the batch in stock is therefore:
DSum("Quantity", "tblStock", "BatchID = 00")

You can then create a query that contains the Product, Batch, and Stock
tables to give you something like you listed in your post.

Now, it might actually get more involved than that, e.g. if you need to
allow for the fact that part of a batch was actually not moved into the
fridge quickly enough, and so it will expire earlier than the rest of the
batch.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ha*******@yahoo.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
Hi all,
What will be the best database design for an inventory control that
uses expiry date for its products.

Over a period of time, a particular product will have many expiry date
and that one particular expiry date could have multiple products

I am thinking of two approaches not knowing which one to go for.

First design is of one product having many expiry dates (a one to many
relationship between product table being on one side and expiry date
table being many side)

Second design is of many to many table for a product and expiry date.

Among one of my many requirements is to get the inventory balance of
products based on product description and expiry date.

Example:
My purchase is as follows
Description Qty ExpiryDate
Product A 100 15th Jan 2006
Product A 100 31st Mar 2006
Product A 100 30th Jun 2006
Product B 200 15th Jan 2006

My sales is as follows
Description Qty ExpiryDate
Product A 25 15th Jan 2006
Product A 50 31st Mar 2006
Product B 15 15th Jan 2006

My report should show me the balance as follows
Description Qty ExpiryDate
Product A 75 15th Jan 2006
Product A 50 31st Mar 2006
Product A 100 30th Jun 2006
Product B 185 15th Jan 2006

I thank you all in advance for your help

Nov 17 '05 #2

P: n/a
Depending on your number of products you can either use the product
code as a key to a subtable containing purchase date, expiration date
and quantities or you can simply use multiple records and treat each
expiration date as a seperate product line.

Unless you can guarentee that product is issued in the order it expires
you need to get the expiration date of any product sold.

Nov 19 '05 #3

P: n/a
ha*******@yahoo.com wrote in
news:11*********************@g14g2000cwa.googlegro ups.com:
Hi all,
What will be the best database design for an inventory control
that uses expiry date for its products.

Over a period of time, a particular product will have many
expiry date and that one particular expiry date could have
multiple products
No, each lot of a product has it's own expiry date. Several lots
of products may have expiry dates that are coincidental.
I am thinking of two approaches not knowing which one to go
for.

First design is of one product having many expiry dates (a one
to many relationship between product table being on one side
and expiry date table being many side)
You should have a products table and a transactions table. in a
one to many relationship.

a lot of a product is received and goes into the transactions
table, with the date of receipt, quantity, expiry date, (vendor
and cost if you want it and a lot number. these records would
get a transaction code of "IN". sell some stock you put the
sales info in the same table, with an "OUT" transaction code.,
the lot number from which you picked the stock and the quantity
taken out of inventory.

A few queries will give you exactly the data sources for your
reports.

Second design is of many to many table for a product and
expiry date.
forget this concept. storing a date value takes no more
diskspace than storing a double precision number, which you
would need as a foreign key to your expiry dates table.
Among one of my many requirements is to get the inventory
balance of products based on product description and expiry
date.


--
Bob Quintal

PA is y I've altered my email address.
Nov 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.