Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    5

    Remembering almost permanent data

    I have a database which among other things is used to keep track of jobs done on ships.

    My [Job] table uses [ShipID] to pull in the relevant data from the [Ship] table.

    Everything works fine, BUT occasionally a ship will change its name. This in itself is not a problem but for historical reasons I need to keep track of what the ship was called when we carried out a particular job.

    At present, as well as recording [ShipID] in my [Job] table I also record [ShipName] which of course is duplicating data

    As I say, this works but deep down I know it is not the way I should do it. However, I cant figure out how I should, suggestions greatfully received

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    If you really need to know that, then your solution may be the most reliable way; it's not unlike recording a product's price as well as its ID on an invoice line.

    An alternative is to have a ship_names table something like:

    Code:
    create table ship_names
    ( ship_id references ships
    , from_date date
    , to_date date
    , name varchar(50)
    );
    Now you can look up the ship's name where the Job date falls between the from_date and to_date.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    In such a scenario, you are not duplicating data by copying the ships name to the jobs table. In one table you are storing the ship's current name. In the other table you are storing the name of the ship at the time the job was performed.
    These are actually distinct pieces of information, since obviously one can change without affecting the other.
    So, as in Andrew's invoice example, you are not violating the principles of normalization by copying this data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2008
    Posts
    5
    Gentlemen,

    Thankyou.

    When it is explained it does seem obvious really. However, I have always felt that taking two pieces of data from the same table in this scenario was somehow 'wrong'.

    It is nice to have my mistake cleared up and to put this particular niggle behind me.

    Again, thankyou.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Definitely not obvious. Its a mistake made by many people who apply the principles of normalization without really understanding the purpose.
    But now you know!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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