Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2012
    Posts
    4

    Unanswered: If Value = Rule Then ... Load Type from FK?

    Okay so I am trying to design a simple database, though I plan on using this as a learning exp to make a huge one.

    I want to design a Package Tracking Application with these tables:

    Table; Property, Property....

    Client; CompanyID (PK), ClientName, PackageID (FK)

    Package; PackageID (PK), CompanyID (FK), LocationID (FK)

    Transaction; TransID (PK), PackageID (FK), LocationID (FK), TransTimeStamp

    Location; LocationID (PK), PackageID (FK)

    -- Child Entity of Location
    --- JobStage; JobStageID (PK)


    I am wondering how to implement this in such a way that certain locations are associated with a certain Job Stage (Not Started, Processing, Complete). I would like to link it so that the stage is transparent to the user. Just by selecting a Location, it will be associated with the corresponding JobStage.

    Here is a simple map of my Relationship as I have it.

  2. #2
    Join Date
    Aug 2012
    Posts
    30
    can you explain rationale behind above design? I see lot of redundancy here. for example why should Client and Package both have other tables ID as FK?

  3. #3
    Join Date
    Aug 2012
    Posts
    4
    I say Tables out of habit.

    I am really learning the Entity Database Modeling structure, and the above Design is more built on Entities rather than actual Tables.

    Sorry for the confusion, as I am a but confused myself.

  4. #4
    Join Date
    Aug 2012
    Posts
    4
    to answer your question more directly;

    Client has the FK of Package so that I can generate a list of packages I have for a given client...

    Packages has the FK of Client so that I can see who the package belongs to

    Location has PackageID as FK so I can list the Packages I have stored in that location as a collection.

    Transaction has PackageID and LocationID as FKs so that I can log which package was transferred to Which Location at what time.

  5. #5
    Join Date
    Aug 2012
    Posts
    30
    i think a more sensible design would be like

    Client; CompanyID (PK), ClientName,other info....

    Package; PackageID (PK),CompanyID(FK), other package details..

    Transaction; TransID (PK), PackageID (FK), LocationID (FK),JobStage, TransTimeStamp

    Location; LocationID (PK), other location details...

    TransactionHistory: TransHistID (PK),TranID(FK),JobStage,StageTimeStamp
    to track history of trsnaction (if required)

    JobStage: jobStageID(PK),StageDesc

  6. #6
    Join Date
    Aug 2012
    Posts
    4
    I do not disagree, but I am still wondering how I would most logically have the DB know simply by a LocationID what the JobStage was... I know I could program this to be done with some Select Case... but I feel like there is a way to do it with the relationships of the database.

    I am willing to spend the extra time to learn this, as this is a learning project... but if I am totally wrong as the Select Case is the most logical way (even if this were large scale) then I would like to know that too..

  7. #7
    Join Date
    Aug 2012
    Posts
    30
    its not by locationid that you know on jobstage. its by means of transactionid

Posting Permissions

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