Database Design suggestion?

[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

 

[Guest]

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

 

[Allen Browne]

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.

 

[Jamie Collins]

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.

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.

--

 

[Guest]

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.

 

[Guest]

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.