Database Design suggestion?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am creating a new database and would like some help/ sugguestion on how to
best design it.
I want to track where the carts are located on a daily basis. There are 26
total cart number 1-26. Each track is either one of the cart and will travel
on it thru 10 opertions. I am thinking the 26 carts could be in check box
form where any of the operation could have more than one cart at a time.

Each cart will always travel thru operation sequence 1-10. I would like the
report to look something like this.

Date Operation Cart 1 2 3 4 5 6 etc...
8/5 A X
8/5 B X
8/5 C X
8/6 A X
 
Do you want only to know where the carts are now or where they were at a
given time in the past? Do you need to know when a given operation started
and ended or only when it started? If you need the history, the answer is
quite different. If you don't need history, I would just use a single table.
Each row contains the current operation and date for one cart.
If you need history, each row needs to contain a date or two dates if you
need start and end times. In this case you will have multiple rows per cart,
one for each current or past operation.

Dorian
 
Cam, you need 3 tables:

Cart table: one record for each of the 26 carts.

Operation: one record for each of the 10 operations.

CartOperation table, with fields like this:
CartID relates to Cart.CartID
OperationID relates to Operation.OperationID
OpDateTime date and time when this cart started this operation.

The 3rd table will have a new record each time a cart moves to a different
operation.

You can interface this with a main form bound to the Operation table, and a
subform bound to the CartOperation table. You add a new row to the
continuous subform each time a cart moves. The CartID control in the subform
will probably be a combo box, where you choose a valid cart number.
 
Cam, you need 3 tables:

Cart table: one record for each of the 26 carts.

Operation: one record for each of the 10 operations.

CartOperation table, with fields like this:
CartID relates to Cart.CartID
OperationID relates to Operation.OperationID
OpDateTime date and time when this cart started this operation.

The 3rd table will have a new record each time a cart moves to a different
operation.
Click to expand...

Further design work required: EITHER put a validation rule on
OpDateTime to ensure the time element is always midnight and include
the column in the compound key (OpDateTime, CartID, OperationID) OR
make the table a valid-time state table with a start and end date pair
on each row (rather than spit the subatomic elements across rows and a
sequenced primary key (i.e. including a table-level CHECK constraint
to prevent overlapping periods for the same cart). For simplicity, and
because the OP mentioned "a daily basis", I'd suggest the former.

Jamie.

--
 
Do you also need to consider down time (repair, etc)? That could be an
additional operation.
Your operation list should not be static, but a table. It should have 3
columns
an autonumber primary key that relates to the activity table, a text
description of the operation, and a boolean field to make the operation
either available or not available.
 
I just want a simple database that I can track the output progress of parts.
I am expecting to at least each operation to complete 2 carts daily. So I
thought doing a option box will tell me how long the cart been sitting in
certain operations.
Once a day, I manually walk the line and record where the carts are located.
 
Back
Top