Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2015

    Database for ships

    I am at a loss when trying to lay out a database for the following scenario.

    A ship is contracted and built a certain date (launch date) and year (launch year) for a certain owner. The ship has a weight, length, name, yard, yard number (and several other specifics). Many of these are common and stay the same throughout the ship's life span.
    But a certain year, this same ship might be sold to another owner and be given a new name.
    And them maybe sold again, and again.

    I know how I'd draw the table if the ship was just built and never sold, but how do I have several instances of the same vessel under different names (and lengths, and weights etc.).

    Any tips to get me started?

    Best Regards
    Geir Rosset

  2. #2
    Join Date
    Apr 2015
    Just trying to revive this as I got no responses. I'll use csv below to lay out one specific ship

    ID, name, year launched, built as name, owner, length, width, HP
    1, Admiral, 1869, Ariadne, Eagle, 254', 36', 168NHP

    This ship was sold to new owners in 1909 but she kept her name. How should I build this to avoid duplicating tons of info. Or is it easier to just give her a new ID for the next owner?

    2, Admiral, 1869, Ariadne, Bryde, 254', 36', 168NHP

    Then she was sold again in 1912 (still kept her name, but lets say it was changed for argument's sake).

    3, Bucentaur, 1869, Ariadne, Alaska Ltd, 254', 36', 168NHP

    I guess what I am struggling with is that it's just one ship, but if I do it like oulined above each ship will have several different IDs instead of having one ID and several owners and names.

    Any help to get me started would be much appreciated.

    Best regards
    Geir Rosset

  3. #3
    Join Date
    Jan 2003
    You would likely have one table where the rows represent the physical ship, and those attributes that never change, then a second table of the names/owners, and other data that do change over time (presumably include the dates the information is valid for, as well). The relationship would be one physical to many owners.

  4. #4
    Join Date
    Nov 2004
    out on a limb
    read up on normalisation
    understand what that is all about
    break apart your data so that you have no repeating elements

    So a ship is built
    it stays in that configuration until its modified/rebuilt
    if you need to track a ships name over time then that needs to be in a different entity.
    never be tempted to duplicate data for what is theoretically the same entity
    so it (may be) fine to say duplicate data for more than one ship built to the same design (but I'd suspect you should have detaisl for each ship)
    its not fine to duplicate data if everything is the same except say the boat name

    in the relation database world you tend to have one to zero,one or many relationships (ferinstance a shipping line may own zero, one or more vessels)
    but the real world often has many to many relationships (ferinstance a vessel may over the course of its life have mulitple names, multiple owners, multiple configurations) a many to many relationship can be a pig in the realtional db word and its often implmented as an intersection table. you defienm an entity for vessesl, an entity for owners and an interesction tabel that holds all the relevant details to that vessel when owned by that owner.

    think of an entity as a holding tank for one set of data
    so you might have an entity for:-
    owners_vessels (a so called intersection table that identifies what vessels are owned by which owners)
    vessel_rebuilds the problem is how do you handle the name change. do you store it as an attribute of say vessel details, does it deserve an entity of its own

    but in principle
    one row per vessel in vessels
    one row per owner in owners
    ..the intersection table identifying the owners_vessels would hold everything pertinent to the vessel during the time of that ownership. so it will have
    the PK from the owners table
    the PK from the vessels table
    say the date it was bought and anything else pertinent to that ownership (say amount paid), but you wouldn't neccesarily have an accquisition date AND disposal date as the disposal date is the next accquistion date.

    but that then leaves the problem of vessel disposal (ie when the ship is sold to another owner, or is scrapped or is lost at sea.
    a way of modellignthat is to, say, register a transfer of ownership to the releavnt category

    ie if you knwo the yard it was scrapped in, record them as the new owners, or create a dummy owner. you coudl expand that dummy owner to identify the type of disposal by assiging a different dummy owner for each type
    disposal:lost at sea, storm
    disposal:act of war
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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