One mistake that I see happen a lot in database design is that people get stuck in visualizing the data in physical terms along with the labels we attach to them. You should try to think more generically. Freezer, box, building, tube, etc. are all just labels assigned to locations. In your case, everything appears to fall together quite nicely. Here is how I would create your main "Inventory" table: Inventory ---------------- InvSeqID ParentID TypeID Quantity LocVertical LocHorizontal MiscAttribute (i.e. Temp)
Types --------------- TypeSeqID Label Capacity Description That should take you most of the way to designing a system that can expand easier without changing table structures or adding tables when you get a new location "Type", like floor, building, complex, etc. Your "Types" table will hold a list of the various "Types" of "locations". This would have an entry for each size tube you use, each size box, freezer, etc. Very easy to had new box sizes, freezer types, etc. The Inventory table will have many self relating (ParentID->InvSeqID) records that you can drill down (or up) to build a view of your inventory. This table structure allows an unlimited amount of "levels" without the need for you need change or add tables. Programming this may seem daunting at first, but most of your programming will just be loops on the ParentID, continuing until there is no more parent ID. I know may not have gone into enough detail for you, but as you said, this has been done 650,000 some odd times. Although usually to create a message board that can have unlimited replies to replies. The attributes you are tracking are just different than a message boards, the concept is the same. And there are a bunch of message board tutorials out there. On Thursday, September 19, 2002, at 06:30 PM, Csaba Kiss wrote: > this is my first mail to the mailing last, since I am new to the > mysql > world. I would like to create a "cell-bank" mysql database. We freeze > down cells in small tubes. These tubes are stored in boxes and the > boxes are in big freezers. Sometimes we freeze 10-20 new tubes with > new samples (i.e. 5 of each). -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
