January 16, 2007 at 1:41 am
Hi,
I am about to design a database for tracking the receipt and issue of items. Each item has a unique number and will be delivered in blocks of either 250 or 500. Because of the number of items, I want to store the unique item numbers by storing the first number in the sequence and the last number of the sequence only i.e. not an individual record for each item number. So far so good. The tricky part starts when items are issued: they may be issued in a full block or in part: in other words, an issue of 250 could be made or an issue of 10 could be made. When the issue is made, again I only want to store the starting number and ending number of the sequence. Here's what the table would look like with some data:
TransType StartSequence End Sequence
RECEIVE 1 250
RECEIVE 251 500
RECEIVE 501 750
ISSUE 1 5
RETURN 3 3
ISSUE 251 500
So, each of the Receipts was for 250 items. The first Issue was for five items (No.s 1, 2, 3, 4, 5). Item No. 3 was returned. The second Issue was for Item 251 to Item 500.
I can calculate the number in stock without any bother but how do I produce a resultset listing each of the item numbers/ groups in stock?
Items In Stock Report
From 3 To 3
From 6 To 250
From 501 To 750
Thanks.
January 16, 2007 at 2:46 am
That's a bit confusing... let's say you have received 250 items, No 1-250. You have issued them in 5 blocks of 50. Items No. 3, 27 and 41 were returned (all together, in one "block"). How do you enter that, as 3 rows of the type RETURN?
Now you want to issue these 3 items. How do you do that? Again 3 separate issues?
Why don't you want to have a row for each item, is it just because there would be too many of them?
How do you know that precisely number 3 was returned and not number 7? Are these "items" labelled? I understand that these 250 items are of the same type (like 250 bottles of milk)... but maybe not? What is the reason why you need to know which precise number was returned?
I'm afraid that without more info on what "items" these are and how the process will work we can hardly give you any useful tips.
January 16, 2007 at 3:14 am
The items are individually labelled i.e. with a barcoded number. So, to return the three items mentioned above would mean three separate Returns. To issue them again would also be three separate Issues. The reason I don't want to list out each number is that there will be approximately 2 million numbers per year. Of the issues, approx. 95% will be full batches of either 250 or 500; Returns will only form about 1% of the transactions. It seems to me that it would be a waste of space to list each individual number hence this approach.
Thanks.
January 16, 2007 at 3:46 am
Hmmm.. let's think about it. Which numbers are on stock? Those, where the last entry for them is not ISSUE (i.e. it is either Receive or Return). Problem is, how to filter that out if you use ranges. Obviously, you could use a Numbers table and join that to your table using BETWEEN StartSequence AND EndSequence. But.. this way you will actually rebuild (in memory) all the rows that you "saved" by using ranges instead of individual rows, every time when you generate this report. Now is that a report that can run once in a time, or will that be required often? Will you have to do this report for all articles (if you have more than one), or just for one article at a time?
My opinion is that 2 million numbers per year is something SQL Server should work fine with, and I would prefer to store the individual rows. Of course, I don't know all the things you have to do with the data... but so far it seems to me that the ranges won't help you much because of the various manipulations you'll have to do to generate reports (most probably not only this report), and I'm almost sure that some of them will be required to return data "online", in real time.
Maybe other people will have some nice solution for you, but I can't think of any right now.. sorry.
January 16, 2007 at 3:59 am
I can guarantee one day they will ask you to store bar code for each item.
Do you plan to leave before that day?
And lets do a little bit of calculations.
2 million rows of 10 bytes each (say, 2 ints + 1 smallint) will give you 20 MB of "wasted space" per year. With bar code it will be 50 MB per year.
I don't think you are still working on i-486 with 2GB hardrive to worry about such amount of space. It's not a problem even for new mobiles.
Do your design properly. Don't cut corners. It will save you months of time and gigabytes of space. And improve you reputation.
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply