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]
