Prev: Web Database
Next: Choices to display dependent on previous choices...
From: JonB on 31 Mar 2010 13:51
Here is my challenge. My work has a wretched almost unsearchable "database".
I am trying to cobble together my own relational database from that
monstrosity so that I can pull the information I need quickly and link it to
other databases we have. My biggest road block is in how we store data what
widgets are inside a batch. Here is the problem:

We make widgets. The widgets are made in batches. The widgets that are in
a batch some times are merged into new batches, split, or destroyed. I
really want to be tracking what happens to a widget, not a batch.

The database stores ONLY batch IDs. It records when a widget leaves or
enters a batch, but there is no way to query “hey, what was in batch 66 at
processing step #10”. The best you can do is find that at process step #1
widgets A, B, and C were put into the batch, at step #6 C was removed, and at
step #9 D was added, so at step #10 widgets A, B, and D must be in the batch.

So the evil work database would looks like this for the above:
Batch, Step, Action, Action Value
66, 1, add widget, A
66, 1, add widget, B
66, 1, add widget, C
66, 6, remove widget, C
66, 9, add widget, D


Here is the question, what sort of methodology should I be using to set up a
nice clean relations table. I want it so that there is a table that more or
less says at step #10, batch #66, and components A, B, and D. In my ideal
world I think I would want it to look like:

Step, batch, component
10, 66, A
10, 66, B
10, 66, D
11, 66, A

Is there a way to do this? I really want to be able to answer the question
of “what widgets were in batch XX at step ZZ”.
From: KARL DEWEY on 31 Mar 2010 15:23
The way i see it is that you need at least three tables.
tblBatch --
BatchID - Autonumber - primary key
DateStart -
DateEnd - other fields as needed

tblWidget --
WidgetID - Autonumber - primary key - each widget id'd either by tag, stamp,
etch, etc.
other fields - special characteristics, tolerances, etc.

tblWidgetBatch --
WidgetBatchID - Autonumber - primary key
WidgetID - number - long integer - foreign key
BatchID - number - long integer - foreign key
TransDate -
Trans - In/Out (Yes/No)
Step - number - long integer
OutTo - Pick list - refinish, calibrate, scrap, ship, stock, etc (These
could have a BatchID)

tblWidget and tblBatch to have one-to-many relationship with tblWidgetBatch.

Use a query to determine want was in a batch at any given time or where any
widget was at a particular instance.

--
Build a little, test a little.


"JonB" wrote:

> Here is my challenge. My work has a wretched almost unsearchable "database".
> I am trying to cobble together my own relational database from that
> monstrosity so that I can pull the information I need quickly and link it to
> other databases we have. My biggest road block is in how we store data what
> widgets are inside a batch. Here is the problem:
>
> We make widgets. The widgets are made in batches. The widgets that are in
> a batch some times are merged into new batches, split, or destroyed. I
> really want to be tracking what happens to a widget, not a batch.
>
> The database stores ONLY batch IDs. It records when a widget leaves or
> enters a batch, but there is no way to query “hey, what was in batch 66 at
> processing step #10”. The best you can do is find that at process step #1
> widgets A, B, and C were put into the batch, at step #6 C was removed, and at
> step #9 D was added, so at step #10 widgets A, B, and D must be in the batch.
>
> So the evil work database would looks like this for the above:
> Batch, Step, Action, Action Value
> 66, 1, add widget, A
> 66, 1, add widget, B
> 66, 1, add widget, C
> 66, 6, remove widget, C
> 66, 9, add widget, D
>
>
> Here is the question, what sort of methodology should I be using to set up a
> nice clean relations table. I want it so that there is a table that more or
> less says at step #10, batch #66, and components A, B, and D. In my ideal
> world I think I would want it to look like:
>
> Step, batch, component
> 10, 66, A
> 10, 66, B
> 10, 66, D
> 11, 66, A
>
> Is there a way to do this? I really want to be able to answer the question
> of “what widgets were in batch XX at step ZZ”.
From: JonB on 3 Apr 2010 01:52
That table structure is fine and is pretty much what I had in mind, but I
guess where I get lost is how to do the query such that I get what I want.

So, if my tblWidgetBatch is set up as you describe has the following:
WidgetID, BatchID, Trans, Step
A, 66, In, 1
B, 66, In, 1
C, 66, In, 1
A, 66, Out, 3
D, 66, In, 5

How do I find out what was in at step 4? I know I can query the batch for
<= 4 and see:

A, 66, In, 1
B, 66, In, 1
C, 66, In, 1
A, 66, Out, 3

But what I really want to see is just B and C.

Further, if I add another table. Say tblInstruction that is:
InstructionBatchID
Instruction
Batch
Tool

So it looks like
1,1,66,WidgetStomper
2,2,66,WidgetPresser
3,3,66,WidgetPainter
4,4,66,WidgetTosser

And I want to know what widgets ran through the WidgetTosser at step 4, how
do I make that query?

I want to see:
batch,widget,instruction,tool
66,B,4,WidgetTosser
66,C,4,WidgetTosser

Thanks for any help.


"KARL DEWEY" wrote:

> The way i see it is that you need at least three tables.
> tblBatch --
> BatchID - Autonumber - primary key
> DateStart -
> DateEnd - other fields as needed
>
> tblWidget --
> WidgetID - Autonumber - primary key - each widget id'd either by tag, stamp,
> etch, etc.
> other fields - special characteristics, tolerances, etc.
>
> tblWidgetBatch --
> WidgetBatchID - Autonumber - primary key
> WidgetID - number - long integer - foreign key
> BatchID - number - long integer - foreign key
> TransDate -
> Trans - In/Out (Yes/No)
> Step - number - long integer
> OutTo - Pick list - refinish, calibrate, scrap, ship, stock, etc (These
> could have a BatchID)
>
> tblWidget and tblBatch to have one-to-many relationship with tblWidgetBatch.
>
> Use a query to determine want was in a batch at any given time or where any
> widget was at a particular instance.
>
> --
> Build a little, test a little.
>
>
> "JonB" wrote:
>
> > Here is my challenge. My work has a wretched almost unsearchable "database".
> > I am trying to cobble together my own relational database from that
> > monstrosity so that I can pull the information I need quickly and link it to
> > other databases we have. My biggest road block is in how we store data what
> > widgets are inside a batch. Here is the problem:
> >
> > We make widgets. The widgets are made in batches. The widgets that are in
> > a batch some times are merged into new batches, split, or destroyed. I
> > really want to be tracking what happens to a widget, not a batch.
> >
> > The database stores ONLY batch IDs. It records when a widget leaves or
> > enters a batch, but there is no way to query “hey, what was in batch 66 at
> > processing step #10”. The best you can do is find that at process step #1
> > widgets A, B, and C were put into the batch, at step #6 C was removed, and at
> > step #9 D was added, so at step #10 widgets A, B, and D must be in the batch.
> >
> > So the evil work database would looks like this for the above:
> > Batch, Step, Action, Action Value
> > 66, 1, add widget, A
> > 66, 1, add widget, B
> > 66, 1, add widget, C
> > 66, 6, remove widget, C
> > 66, 9, add widget, D
> >
> >
> > Here is the question, what sort of methodology should I be using to set up a
> > nice clean relations table. I want it so that there is a table that more or
> > less says at step #10, batch #66, and components A, B, and D. In my ideal
> > world I think I would want it to look like:
> >
> > Step, batch, component
> > 10, 66, A
> > 10, 66, B
> > 10, 66, D
> > 11, 66, A
> >
> > Is there a way to do this? I really want to be able to answer the question
> > of “what widgets were in batch XX at step ZZ”.
From: JonB on 3 Apr 2010 02:51
Actually, to clarify, I want to be able to query and get:

widget,batch,instruction,tool
A,66,1,WidgetStomper
A,66,2,WidgetPresser
A,99,3,WidgetHeater
A,99,4,WidgetFreezer

I really want to be able to see what tool each widget has gone through, but
we only track what goes into a tool by batch.
From: KARL DEWEY on 5 Apr 2010 11:23
You need TransDate so as to see 'what was when'.

tblInstruction to have these --
InstructionID
Instruction
Tool

Either add InstructionID to Step or Transaction.

--
Build a little, test a little.


"JonB" wrote:

> Actually, to clarify, I want to be able to query and get:
>
> widget,batch,instruction,tool
> A,66,1,WidgetStomper
> A,66,2,WidgetPresser
> A,99,3,WidgetHeater
> A,99,4,WidgetFreezer
>
> I really want to be able to see what tool each widget has gone through, but
> we only track what goes into a tool by batch.
 |  Next  |  Last
Pages: 1 2
Prev: Web Database
Next: Choices to display dependent on previous choices...