I''m working on an inventory management site and have been trying to
think through how to model my data.
Specifically, my inventory is cell lines in freezers (I work in a cell
biology lab) and I have the problem that different freezers have
different configurations. I was thinking I would have two tables
describing the freezers:
Table Freezers
id
description
Table Divisions
id
freezer_id
level (1..5)
label ("Shelf", "Column", "Box", etc)
scheme ("A,B,C...", "1,2,3...", "i,ii,iii...")
quantity (integer)
start (integer)
So, if I had a freezer with 5 shelves, with 5 racks on each shelf,
that would correspond to one entry in the Freezers table and two
entries in the Divisions table
(id,freezer_id,label,scheme,quantity,start)
(1,1,"Shelf","1,2,3...",5,1)
(2,1,"Rack","1,2,3...",5,1)
I think this should work... the tricky bit is how to slot samples into
freezers. I was thinking I would a location table with:
Table Location
...
freezer_id
path
...
where path is a period-delimited string such as ''1.1.A''
specifying a
particular location; ''1.1.A'' might mean Shelf 1, Rack 1, Box A
depending on how the divisions for a particular freezer are set up.
Does this make sense? Is there some better way of doing this?
Thanks,
Ryan
On Wed, 2005-03-23 at 15:12 -0500, Ryan Raaum wrote:> I''m working on an inventory management site and have been trying to > think through how to model my data. > > Specifically, my inventory is cell lines in freezers (I work in a cell > biology lab) and I have the problem that different freezers have > different configurations. I was thinking I would have two tables > describing the freezers: > > Table Freezers > id > description > > Table Divisions > id > freezer_id > level (1..5) > label ("Shelf", "Column", "Box", etc) > scheme ("A,B,C...", "1,2,3...", "i,ii,iii...") > quantity (integer) > start (integer) > > So, if I had a freezer with 5 shelves, with 5 racks on each shelf, > that would correspond to one entry in the Freezers table and two > entries in the Divisions table > > (id,freezer_id,label,scheme,quantity,start) > (1,1,"Shelf","1,2,3...",5,1) > (2,1,"Rack","1,2,3...",5,1) > > I think this should work... the tricky bit is how to slot samples into > freezers. I was thinking I would a location table with: > > Table Location > ... > freezer_id > path > ... > > where path is a period-delimited string such as ''1.1.A'' specifying a > particular location; ''1.1.A'' might mean Shelf 1, Rack 1, Box A > depending on how the divisions for a particular freezer are set up. > > Does this make sense? Is there some better way of doing this?I''ll take a stab at this since I didn''t see anyone else jumping on it. I''m also coming at this from having been in inventory management before. A location should be just a list. The location label is something for making human life easy. Do you can have a locations table that just enumerates all locations. Then you would have an inventory table that maps your inventory to a location_id. This wouldn''t work if your application needs to know physical location to keep from mixing certain items too close to another item. -- Steven Critchfield <critch-wQLwMjUOumVBDgjK7y7TUQ@public.gmane.org>
Steven Critchfield wrote:> A location should be just a list. The location label is something for > making human life easy. Do you can have a locations table that just > enumerates all locations. Then you would have an inventory table that > maps your inventory to a location_id. > > This wouldn''t work if your application needs to know physical location > to keep from mixing certain items too close to another item.When I''ve done things like this before, for stuff like physical records management, I''d build out the locations into a tree. A simple way is to have a location table something like: id type ("shelf", "drawer", "freezer", ...) parent_id (relates back to ''id'' on this table) number (or name, depending on how the individual items are labelled) That would let you: - treat it like a simple list when you wanted to. - you could generate the descriptive names by just walking back up the tree appending the number & type. Kind of like a pathname. - it also means you can check whether 2 locations are on the same shelf, or in the same freezer, etc. If the individual location types differ in terms of what attributes they need (e.g. perhaps a freezer needs a "power consumption" value), then I''d consider having seperate additional tables for each type, just to hold those extra attributes. Cheers, Kevin
Kevin wrote:> When I''ve done things like this before, for stuff like physical records > management, I''d build out the locations into a tree. A simple way is to > have a location table something like: > > id > type ("shelf", "drawer", "freezer", ...) > parent_id (relates back to ''id'' on this table) > number (or name, depending on how the individual items are labelled) >Intriguing. I can definitely see the advantages in this approach. How would I allow data entry? I can''t possibly have a single pull down list (I calculate that there are >50,000 separate locations in a single freezer). And it doesn''t seem very elegant to me to have to pick one level at a time progressing through 4-5 entry screens. Would I have to do some complex javascript thing? Thanks, Ryan
On Thu, 2005-03-24 at 14:25 -0500, Ryan Raaum wrote:> Kevin wrote: > > When I''ve done things like this before, for stuff like physical records > > management, I''d build out the locations into a tree. A simple way is to > > have a location table something like: > > > > id > > type ("shelf", "drawer", "freezer", ...) > > parent_id (relates back to ''id'' on this table) > > number (or name, depending on how the individual items are labelled) > > > > Intriguing. I can definitely see the advantages in this approach. > > How would I allow data entry? I can''t possibly have a single pull > down list (I calculate that there are >50,000 separate locations in a > single freezer). And it doesn''t seem very elegant to me to have to > pick one level at a time progressing through 4-5 entry screens. Would > I have to do some complex javascript thing?You are correct that a drop down would be useless. From experience in warehousing, the information is usually keyed in via the label you assign to the location. This also works well later on if you want to use a scanner and barcodes as the scanner will fill in that section of the form with what it reads. I''m not familiar yet with RFID inventory management yet, but I assume it would work similarly as a barcode reader in that it will fill in a field with the information available at the time it is read. -- Steven Critchfield <critch-wQLwMjUOumVBDgjK7y7TUQ@public.gmane.org>
Here is my advice: Use a text box, let people write a note about where the item is. This should be your first step, chances are you will never need more. Do the simplest thing which can possibly work. -- Tobi http://www.snowdevil.ca - Snowboards that don''t suck http://www.hieraki.org - Open source book authoring http://blog.leetsoft.com - Technical weblog