Background: * MySQL5 - MyISAM tables * Customer is a pallet creation company--they manufacture pallets and then treat the pallet (heat/dip/other) before shipping to customer. * 3 tables in focus for this: -- ORDERS: actual pallet orders -- QUEUE: orders sent to the treatment area -- this is a MANY child for the ORDERS table -- QTY_IN_QUEUE: it's basically the SUM on the QUEUE.QTYs for each order so that I don't have to always do a SUM of the QUEUE table to know the total quantity sent to the treatment area. This allows me to a have a simple 1:1 INNER JOIN query that quickly returns the sum qty from the ORDERS table
I've had the QTY_IN_QUEUE table crash twice on me in the past few months, and I wanted to toss an idea out there to see if my idea for a different design is good or not. The QTY_IN_QUEUE table is basically very small, and as I said, serves as a "shortcut" table to tell me the SUM from QUEUE for the given order. Here's it's structure: iid int PK not null unsigned autoinc iorderid int not null unsigned itotqty int not null unsigned tadded timestamp default=CURRENT_TIMESTAMP not null As the company finishes creating the pallets, they send off the finished pieces to the treatment queue. I use an INSERT trigger on the Orders table to create a record in the QTY_IN_QUEUE table when an order is created with a default qty of 0. That way, I can use an INNER JOIN between ORDERS and QTY_IN_QUEUE and instantly know the total amount. Because I'm using an INNER JOIN so the existence of the record is a must. I use INSERT, UPDATE, and DELETE triggers on the QUEUE table to update the sum in QTY_IN_QUEUE accordingly. Do you see any flaws in this approach? My first inkling (after reading http://dev.mysql.com/doc/refman/5.1/en/corrupted-myisam-tables.html online) is to switch the table to InnoDB, but I'm open for other suggestions as well. TIA! --Michael _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
