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 do

create 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)

end

If 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 more
specific 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 your
basic 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, to
both, to stocks?

for example, color or size is relevant to determine stocks and is related to
the 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 the
transaction nor the product.

My point is, a database design can be a complex task, and the hability an
application 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 no
programming (i think).

mpneves

On Wednesday 18 January 2006 22:55, you wrote:
 Thanks Ed.  That's another good idea.  The consensus I'm getting is to
create one table that stores unchanging data about the product and > another
 that 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 a
 product 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 in
the same transaction table, but then I'll end up with duplicated data. > For
 example,

 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.
> >When parts are received another transaction is entered for that part > >with
>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]

Reply via email to