Re: Product/asset tracking database with history per item
Posted by: Rick James
Date: December 03, 2010 12:11AM

Start with the raw data:

Table: Item
Fields: ID, Type (computer, dongle, etc), Cost, Purchase date, Manufacture, link to warranty, etc

Table: Warranty
...

Table: ItemTracking
Fields: ID (for this table), Item_ID (id of item), Datetime (of move), Location (someone's cube, repair shop, junk yard)
Note: the Datetime is when the item moved into the Location.

That gives you all the data you need, correct? Granted, it is messy to see which Computers a Dongle has been used with, but the data is there.

Lecture:
* At one end of the spectrum is the raw data.
* At the other end is the output report.
* In the middle could be summary information that helps you transition from raw data to report. (Now to discuss that)

From the raw data (ItemTracking), you can deduce what is in each Location on each day. Better yet, think about deriving:

Table: WhatsWhere
Fields: ID (PRIMARY KEY), Location, FromDT (DATETIME), ToDT, Item_ID
INDEX(Location)
INDEX(Item_id, FromDT)
INDEX(FromDT

That 2nd index lets you efficiently SELECT the history of an item.
Then you can turn around and find what else was in the same Location during the given timespan (last index).

Ponder all that. Did I cover what you needed? Can you take it from these sketchy notes? (Sorry, I don't have book to sell you.)

Options: ReplyQuote


Subject
Written By
Posted
Re: Product/asset tracking database with history per item
December 03, 2010 12:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.