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
