Hi Dan,It would be a pretty large table of changes, but this solution would work, if as you say, I don't mind making those sacrifices. Something to think about. Thanks.
Ian At 03:59 PM 1/20/2006 -0600, Dan Buettner wrote:
Perhaps you could use database triggers to keep track of these changes. I also think there's a way you could make the change tracking a little neater if you don't mind sacrificing some SQL functionality and storing everything as text.If you create a table "product_changes", with 4 columns - "id", "change_date", "change_type", and "current_value", you could add one or more entries each time or more aspects of your product change.If your salesrep changes, set your change_type = "salesrep" If your color changes, set your change_type = "color". If your size changes, set your change_type = "size". If nothing changes, then add no entry.You could have the database track this for you with triggers, so you wouldn't even have to do . I haven't used triggers with MySQL so I'm sure my syntax is off but here's a rough outline of what you could docreate trigger ProductUpdateTrig on Product for update as begin if (newsalesrep != oldsalesrep) insert into product_changes values (now(), 'salesrep', oldsalesrep) if (newcolor != oldcolor) insert into product_changes values (now(), 'color', oldcolor) if (newsize != oldsize) insert into product_changes values (now(), 'size', oldsize) endIf you wanted to keep track of additions and deletions you could create similar insert and delete triggers.Note that you would likely have to store the values as text even if they were originally numeric or DATETIME, in order to be able to use a simple table to keep track of all the different kinds of changes.Hope this helps, Dan At 4:45 PM -0500 1/20/06, Rhino wrote:Ian,If I'm not mistaken, you started this conversation yesterday. I've been watching the back-and-forth haphazardly and not really absorbing the full details so forgive me if someone has already asked this and you've answered it.My concern, in hearing you state your problem, is that some of the stuff you want to track just doesn't seem that important or, to put it another way, they just don't seem like the kinds of things that a business will really care that much about.For instance, this note mentions that the size or colour of a box has changed and you want to track that. Frankly, I'm having trouble believing that your management really _needs_ to track that kind of micro-change. Why would they care? Surely their major concerns must be things like sales of goods, profits, and inventories. What difference does the colour of the box make? Do you sell more widgets when they are in blue boxes than when they are in green boxes? Now, at some level, the packaging probably _does_ matter; I'm sure packaging experts will be able to trot out stories about how sales of widgets increased 14% when the box was changed in such-and-such a way. But do _you_ or your company really care about this enough to track the details about the packaging for every single item you stock? Or are you doing a detailed study to try to prove that the packaging really does make a difference of so many percent in sales? Otherwise, I'm at a loss to understand why you'd track that much detail.I caught glimspses of other requirements in the other notes that had comparable requirements; some of them struck me as things that were just not typically tracked in computer systems.I'm not saying you couldn't make a case for any of these requirements; maybe they are all essential for your project. But is it possible that you've taken a "wouldn't it be nice if we could track XXX?" remark that someone made and turned it into a do-or-die requirement? Is is possible that some of these requirements just aren't that important and could be omitted with no important loss of functionality?If you give this due consideration, you may find that a lot of your problem evaporates and the rest gets simpler to handle.Just a general observation made by a disinterested third party; ignore it if you like :-)Rhino ----- Original Message ----- From: "Ian Klassen" <[EMAIL PROTECTED]> To: "Marco Neves" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com> Sent: Friday, January 20, 2006 3:49 PM Subject: Re: Database design help Marco, Thanks for your help. I created this example to try to simplify my real world problem. Clearly I didn't provide enough detail. Keeping with my example, essentially I'm looking at product details that change over time. Let's say I'm keeping track of boxes. Over time, the color or size of the box might change. At any particular time I want to take a snapshot for a box and see what color and size it is. I could have a box table that holds data that doesn't change and another that contains the changing data such as: box_id | name 1 | Big Box box_id | date | color | size 1 | 2006-01-01 | blue | 20 // start off with blue boxes that are 20 in size 1 | 2006-02-01 | red | NULL // boxes are now red but same size 1 | 2006-03-01 | NULL | 30 // boxes are still red but are now 30 in size Or I could break off each field that changes into it's own table. Any recommendations? Thanks again. Ian At 12:35 AM 1/19/2006 +0000, Marco Neves wrote:Ian,I'ld like to help you, but a more specific db design would depend on morespecific description on your application needs.What I can say is that you need to adapt your database to your reality.What I got til now is that you need a product table, where you can store yourbasic information on products.You say you have other information, but I could understand several things.1- That other information is related to the product, to the transaction, toboth, to stocks?for example, color or size is relevant to determine stocks and is related tothe product, and so is relevant to the transactions also.The sale rep is relevant to transaction, but not to the product.sales rep comission is relevante to the sales rep, but not to thetransaction nor the product.My point is, a database design can be a complex task, and the hability anapplication will have to provide solutions to the real world depends, before anyother thing in that database design.The is the point where almost all analisys most be done, and almost noprogramming (i think). mpneves On Wednesday 18 January 2006 22:55, you wrote:> >When parts are received another transaction is entered for that part > >withThanks Ed. That's another good idea. The consensus I'm getting is tocreate one table that stores unchanging data about the product and > anotherthat stores transaction details. The problem I'm still having is how to efficiently handle more than one changing value.As an example, let's say I want to keep track of not only the quantity > of aproduct but who the sales rep for that product is. While the quantity would change much more frequently than the sales rep I could put both inthe same transaction table, but then I'll end up with duplicated data. > Forexample, date | product_id | quantity | rep 2006-01-01 | 1 | 100 | rep 1 2006-02-01 | 1 | 98 | rep 1 2006-03-01 | 1 | 98 | rep 2 2006-04-01 | 1 | 50 | rep 2 Alternatively, I could create one table for the quantity and another for the sales rep. date | product_id | quantity 2006-01-01 | 1 | 100 2006-02-01 | 1 | 98 2006-04-01 | 1 | 50 date | product_id | rep 2006-01-01 | 1 | rep 1 2006-03-01 | 1 | rep 2 This seems to be the cleanest solution, other than requiring a table for every field that I want to track. Ian At 02:36 PM 1/18/2006 -0800, Ed Reed wrote: >I built my inventory system like this, >>I have a products table that contains all the information specific to > >each>part, less the quantity, i.e. Part Number, Description, Vendor, Color, >Weight, SKU number, etc... > >Then I have another table that is my Inventory Tranactions Log that is >just the following > >Date, ProductID, Qty, TypeOfTranacstion, Comment > >The inventory for each part may adjust daily or not. When parts are >removed/sold the transaction log gets a record for that product and the>number of parts that were sold and the type of transaction that > >occurred.>the quantity received and the type of transaction that occurred. When > >we>close the store and want to take a full inventory we first run a report>that get the sums of all the transactions for each product and that > >tells >us what should be on the shelf according to the database. Then we > >verify >or adjust the qty for each product on the shelf by adding a record to > >the >transaction log indicating the quantity and the type of transaction > >that>occurred. >>When we want to see the values in the inventory its a very simple > >report>to get the sums for each product. > >- Hope that helps. > > >>> Ian Klassen <[EMAIL PROTECTED]> 1/18/06 10:09:55 AM >>> > >Hi all, > >I'm trying to figure out a solution to the following problem. >>Let's say I have a store with various products. I take inventory of > >these>products on different days. At any given time I want to view what the >inventory is for the entire store. I also want to know whether the>inventory in the result was taken on that day or was carried forward > >from> a previous date. I may also have to make changes to the inventories > previously recorded. I have a few solutions, none of which I'm really > happy with. >>The first is to create rows that contain the inventory for each product > >on >a given day. If no inventory was taken for a given product then leave > >the>column null. > >date_of_inventory| product a| product b| product c >2006-01-02| 100| 50| 25 >2006-01-03| NULL| 45| NULL >2006-01-05| 78| NULL| 22 > >To obtain the inventory on any given day I would have to query each > product and find the most recent time that it was updated. With this > solution there is a lot of wasted space caused by the NULL's. > >Another solution would be to have a start and end date for when the >inventory is valid such as: > >start_date| end_date| product a| pa_up| product b| pb_up| product c > >| pc_up > >2006-01-02| 2006-01-03| 100| TRUE| 50| TRUE| 25 | TRUE >2006-01-03| 2006-01-05| 100| FALSE| 45| TRUE| 25| FALSE >2006-01-05| 2006-01-05| 78| TRUE| 45| FALSE| 22| TRUE >>With this solution I can quickly retrieve the inventory on any given > >day >and see what inventory was taken on that day (which product update > >columns>are set to TRUE). However, I see the update side of this as a nightmare >(especially considering I'm duplicating data). >>A third solution could be breaking each product into its own table. > >This >would eliminate the issues with the first two solutions but I would end > >up>with hundreds of tables which I would like to avoid. >>Any help on the direction that I should go would be greatly > >appreciated.> >Ian > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]-- AvidMind, Consultadoria Informática, Unipessoal, Lda. Especialistas em OpenSource http://www.avidmind.net OBC2BIP-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: 19/01/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: 19/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
