Re: Help needed with fixed size table database design!
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
Help needed with fixed size table database design!
Hello guys, 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). When we pick them up we do it usually one by one. Since the size of the boxes and the freezers are constant, the tables that I should use should also be constant (right?). I am not sure how I should design my database. That's why I would like to ask for help. I thought of using the following tables: Samples (contains the data about the samples) Boxes (I am not sure about this) It should contain how many rows and columns are in the box? Freezers (Freezers have racks that holds the boxes and these racks are arranged in rows and columns again)I guess this table should contain the number of racks, rows, and columns, location, temperature) I am stuck somewhere here. I am not too sure how I should proceed. Should I create a freezing table that contains Sample_id, Box_id, Freezer_id...? The database should automatically put the tubes in the boxes until they are full and then move to another box by itself. If is thaw one samples it should remove those samples from the box and mark it empty. I am quite sure that this kind of database have been already created 652,342 times. Nevertheless, I would appreciate any type of help for a newbie, with links or tutorials for these kind of problems. Thanx -- Best regards, Csaba Kiss [EMAIL PROTECTED] MTC Karolinska Institute Stockholm, Sweden tel:+4687286259 fax:+468330498 ICQ:7911383 _ This message was created using The Bat! v1.60q on Friday, September 20, 2002. - 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
Re: Help needed with fixed size table database design!
Hi, On Fri, 2002-09-20 at 08:30, 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). When we pick them up we do it usually one by one. Since the size of the boxes and the freezers are constant, the tables that I should use should also be constant (right?). I am not sure how I should design my database. That's why I would like to ask for help. I thought of using the following tables: Samples (contains the data about the samples) Boxes (I am not sure about this) It should contain how many rows and columns are in the box? Freezers (Freezers have racks that holds the boxes and these racks are arranged in rows and columns again)I guess this table should contain the number of racks, rows, and columns, location, temperature) I am stuck somewhere here. I am not too sure how I should proceed. Should I create a freezing table that contains Sample_id, Box_id, Freezer_id...? Rule #1: a database is not a spreadsheet. Particularly with a RELATIONAL database. Also, you want to prevent duplicating information (in relational terms: normalise). You could create a freezer table, a box table and a sample table. Each box (=row in box table) has a reference to the freezer_id, and each sample has a reference to its box_id. So you build relations between the tables. If you need to store the location of a box within a freezer, you can add its row/col coordinates as extra columns in the box table. That way each box has its own coordinates with it as well as its freezer_id. Easy! Temperature... per freezer or per indivual box? Put in the appropriate table (i.e. freezer if per freezer. That way you only have to store/update it in one place). The database should automatically put the tubes in the boxes until they are full and then move to another box by itself. That is something for your application to do. You can find out how many samples each indivual box has, so where to add new samples, and you can also decide when to start a new box. If is thaw one samples it should remove those samples from the box and mark it empty. In the above schema, you would simply delete the sample from the sample table. In the end, a box would have no samples referring to it, which implicitly makes it empty. So, start thinking in relational terms. Your project actually makes this quite easy because it itself already has clear relations (samples in a box, boxes in a freezer, etc). Good luck! You may also wish to consider MySQL training, to quickly get to grips with these concepts. See http://www.mysql.com/training/ for details on currently scheduled training courses. Regards, Arjen. -- MySQL Training, Support, Licenses @ https://order.mysql.com/?marl __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ www.mysql.com - 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
