Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    172

    Unanswered: inventory database

    I have a project that must track records stored in a warehouse. I have several designs in use, based on the client requirements, but I would like a 2nd opinion on how to design the table structure and relationships. Here are a few of the key database functions:

    File (manila folder) records are stored in boxes
    Boxes can contain multiple files
    Files can be checked out and checked back in to a box
    Files can be re-assigned from one box to another
    Files may also be permanently removed from a box and transferred out of the warehouse
    We should be able to track the history of file movement from box to box
    We should be able to move a file into a box for the first time, move between boxes, or pull the file from the box forever

    Where I've been unclear about is how to best design the table structure. I thought about having a table for the files and one for the boxes and then having a new table that holds the locations (box id and file id). But I'd like some input on this. Should the location table also function as a history table? Or should I keep a table of current locations, and then create a historical transaction table of past file changes?

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by JoeG
    Should the location table also function as a history table? Or should I keep a table of current locations, and then create a historical transaction table of past file changes?
    IMHO, You will have more flexability by keeping a transaction log in a separate table. You can use an event code to tell what action takes place: LocMove, CheckOut, CheckIn. You can also time stamp transactions. If you want the history of Location query on code 'LocMove', Checkouts query on Transaction code 'CheckOut'.

    Files have a location, They can only be in one location at a time, One File One location. Can't you keep the location in the File Table?
    ~

    Bill

  3. #3
    Join Date
    Dec 2003
    Posts
    172
    yes I can keep the location with the file id in the files table. for some reason I figured I might not want to do that because there would be other related transactions that should stay separate from the files themselves.

    in other words I saw the files table as mostly static information (like a product description) rather than as a constantly changing location file.

    maybe I'm just making this thing more complex than it needs to be

    thanks for your thoughts. still open to ideas if anyone else wants to chime in.

    jg

  4. #4
    Join Date
    Dec 2003
    Posts
    172
    okay I know why I wanted locations separate - each location change would generate a new date-filed entry and a single file table would only allow 1 entry so I'd have to end up putting historical locations in another table anyhow.

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by JoeG
    okay I know why I wanted locations separate - each location change would generate a new date-filed entry and a single file table would only allow 1 entry so I'd have to end up putting historical locations in another table anyhow.
    Files will be a main eliment of your database, there should be a "Unique" list of files, w/unique FileID field. The 'Date Filed' and 'Location' would be eliments of the File record. If you need to track location changes capture that data in a separate table. This will allow you to query log using the fileID as the criteria.

    Adding more than one record for a File entity will complicate things.
    ~

    Bill

  6. #6
    Join Date
    Dec 2003
    Posts
    172
    your comments helped a lot. thanks again.

  7. #7
    Join Date
    Jul 2003
    Posts
    74

    Inventory DB Design

    On the basis that a picture is worth 1,000 words, here is a Data Model on the Database Answers web site that is somewhat related and might be interesting :-
    http://www.databaseanswers.org/data_...oxes/index.htm

    B.Dimple
    DBA

  8. #8
    Join Date
    Dec 2003
    Posts
    172
    yes that's a great link and very helpful. looks like my design was on track after all. thanks all. I think I've got what I need now.

    JG

Posting Permissions

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