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]
