Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006

    Unanswered: Data modeling question


    I am looking for help to model the following situation as I am new to data modeling and I am in over my head.

    I have a lot table (lotID, quantity, attributes)

    Lots which are not treated are then shipped and sold as lots.

    Treated lots are given a new identity called a slot. There are 3 possibilities:
    1. A lot may be given a treatment and then it has a single unique slotID
    2. Multiple lots may be combined, given the same treatment, and then these lots all have the same single slotID.
    3. A lot may be split (quanity wise) into multiple slots each slot having its own uniqueID.

    This results in 2 tables:
    Slot (slotID, quantity, attributes) and a Lot-Slot (LotSlotID, slotRef, LotRef).

    So if lotIDs 1 and 2 are combined into slotID 1, that would result in entries in the Lot-Slot table as (1, 1, 1) and (2, 1, 2).

    If lotID 3 is to be split into 2 sublots then Lot-Slot would have (3, 2, 3) and (4, 3, 3)

    Now slots may also be treated, combined and/or split into new slots so I have a Slot-Slot table to track this (SlotSlotID, OrigSlotRef, NewSlotRef).

    Now, my cucustomer is able to ship and sell slots. However, when shipping or selling these slots, he may ship part of a slot and/or sell part of a slot since each slot has a quantity.

    So if slotID4 has a quantity of 500, he may ship 100 to Italy, 50 to HongKong and so on. He may then sell 20 of the slots from Hong Kong and 40 of the slots in Italy to one customer since he always sells from a location. The slotID 4 must be preserved in all of these transactions and be available until either all slots in it are sold or until slotID4 gets combined or split into new slot numbers.

    I was thinking that the Slot table should then have the following fields: OriginalQuantity, QuantityAvailToShip, QuantityAvailToSell.

    Then I have separate tables to track the selling and shipping of the lots and slots.

    But it is here that I get lost on how to keep track of where the slots are and what is available to sell.

    I have a shipSlotHead table (shipHeadID, date, consignee, consigner, etc.) and a shipSlotLine table (ShipLinesID, ShipHeadRef, SlotRef, Quantity).

    Then I have a SaleSlotHead table (salesID, customer, date, billing address, etc.) and a SaleSlotLine table (SaleLineID, SalesIDRef, SlotRef, Quantity)

    Does anyone have any suggestions if there is an easier or better way to track this because I fear that the queries then become complicated?

    Can anyone help me?

    Kind regards,

  2. #2
    Join Date
    Jun 2002
    Mpls/St.Paul area
    Lot traveler databases are notoriously difficult because of the splitting and combining and rework of partial lots. You have to decide how specifically you really want to track items.

    table 1
    table Name: tblOriginalLot
    1. O_lotID: autonumber
    2. O_quantity: integer
    3. O_attributes: text/byte? What are the choices?
    4. O_treated: boolean (yes/no) consider adding this field

    consider nameing the fields with a prefix telling what table they are in

    table 2
    table Name: tblSlot
    1. S_slotID: autonumber
    2. S_AssignedSlotID: integer Allow duplicates
    3. S_O_lotID: integer (you recording the lot ID from where it came)
    4. S_quantity: integer
    5. S_attributes: text? What are the choices?

    S_AssignedSlotID: This would allow you to have multiple records to handle the case when one slot number is created from a batch of lots
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Mar 2006
    Hi Jpshay,

    Thank you for very much for replying. I don't know what a lot traveler database is and have had no luck googling it. Is this a well known problem?

    The attributes I did not list because they are not so important but they are things like purchase location, type, category, group, density, quality, colour, and so on which can be changed due to a treatment.

    I think these 2 tables look good but then wouldn't I need another tblSlot table to keep track of slots which have been split and combined (changing S_O_LotID to a S_O_SlotID)?

    However I still am in the dark about how to track the shipping and selling of individual pieces in these slots (sub-slots)?


  4. #4
    Join Date
    Jun 2002
    Mpls/St.Paul area
    By 'lot traveler' I merely meant to refer generically to the information that moves with or is compile during the production of a lot. The main point from my first statement was sympathy for the problem.

    I think I understand about the attributes. I thought it was one field, and that the information in that field provided the link to other records.

    New question. How many times can a lot be treated?
    Is it possible for a lot/slot to be treated again and a again?

    Regarding the slot table containing OriginalQuantity, QuantityAvailToShip, QuantityAvailToSell. I think I wouldn't have these fields. They would have to be maintained dynamically. Ultimately you would want to know where all the lots went.

    New comments.
    I haven't commented yet on sales. Your ideas sound good. I don't know if I understand fully the difference between head and line. I would do something like this.

    tlkpCustomerContact (a lookup table containing shipping and billing addresses)
    sbTbl (records the source for lots.*)
    tblInvoice (contains addresses, date, final quantity subtotals and description of sales)

    *This would be a one to many table. One sale may have one or more records from tblOriginalLot and one or more records from tblSlots.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts