Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Database Schema for a boat

    Hi,

    i am trying to come up with a schema for a databaes. It's been a while since I have done normalization on a schema, so I could use some help.

    Situation:

    There are boats.
    A boat is build in a shipyard.
    A shipyard has an owner.
    A boat is bought by an owner.
    When a boat is bought by an owner, I still need to see from which shipyard the boat was bought.

    A fairly simple situation you might say, by maybe my head isn't clear.
    I have attached a picture with a schema I came up with.

    I don't think this schema is the best solution.

    In this situation a shipyard has an owner (which is correct) and the owner has a boat (which is also corrcet). Also the shipyard has a boat (could be correct, if the boat is not sold yet).

    I don't think I can easily get a query that can tell me if a shipyard is still the owner, or the boat has been sold.( of course it can be done with attributes).

    What would you guys come up with as a schema. I have another schema, but will not show so I don't blur your minds.

    thanks
    Attached Thumbnails Attached Thumbnails dbs.gif  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by playah
    I don't think this schema is the best solution.
    what's wrong with it?

    it models what you told us perfectly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2008
    Posts
    3
    I'm not seeing the table structure. For example:

    There are 3 owners.

    John, Peter, Mike

    John and Peter are regular boat owners. They have bought a boat.
    Mike runs a shipyard, he makes the boats.

    There are 3 boats.

    Misty, Cloud 9, Sun

    Misty and Cloud 9 are sold to owners John and Peter, Sun is not sold yet and remains in the shipyard.

    There are 2 shipyards.

    Old Yard, Best Boats Inc.

    Old Yard manufactured the boats Misty and Cloud 9.
    Best Boats Inc. manufactured the boat Sun.

    Let's put this in a table structure:

    Owners
    id name
    1 John
    2 Peter
    3 Mike

    Boats
    id name
    1 Misty
    2 Cloud 9
    3 Sun

    Shipyards
    id name
    1 Old Yard
    2 Best Boats Inc.

    owners-boats

    ownerid shipyardid boatid
    1 1 1
    2 1 2
    3 2 3

    in text:

    owners-boats
    ownerid shipyardid boatid
    John Old Yard Misty
    Peter Old Yard Cloud 9
    Mike Best Boats Inc. Sun

    In this situation, I cannot tell if:
    a) Mike is an owner of a shipyard;
    b) A boat is sold or not.

    Thanks.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    well the owner of the yard is arguably an attribute of the yard.. that should handle how you identify the yard owner

    presumably if the owner of the boat is the shipyard then de-facto its not sold, alternatively if there is no row in owners-boats its not sold

    I think there may be a problem if say the same boat passes through the boat yard more than once (say its taken in by the manufacturer in part exchange). but as to whether thats relevant ir not depends on your assignment
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2008
    Posts
    3
    Well, maybe I should just rename the 'owner' table to 'persons'.

    I have added a new schema picture.

    Persons
    id name
    1 John
    2 Peter
    3 Mike
    4 Sandra

    Boats
    id name
    1 Misty
    2 Cloud 9
    3 Sun

    Shipyards
    id name personid
    1 Old Yard 3
    2 Best Boats Inc. 4

    persons-boats
    personid boatid
    1 1
    2 2


    shipyard-boats
    shipyardid boatid
    1 1
    2 2
    2 3

    I'm going to think out load now, so please correct me if I'm missing something.

    This way, everybody is a person. A shipyard has an attribute for PersonID to reflect the owner of the yard.

    Persons-Boats connects persons to boats.

    This way I can always know from which shipyard the boat was bought.
    If a boat-owner sells his boat to another person I can add an attribute to persons-boats 'previousownerId'. for the history.

    What will happen if a shipyard buys back a boat and sells it again? or maybe the owner of the yard keeps the boat for himself? I want full history, so previous owner should be recorded.
    Attached Thumbnails Attached Thumbnails boats.gif  

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by playah
    What will happen if a shipyard buys back a boat and sells it again? or maybe the owner of the yard keeps the boat for himself? I want full history, so previous owner should be recorded.
    You need some dates:
    persons-boats
    personid boatid start end
    1 1 2007-01-23 2008-04-30
    2 1 2008-05-01
    ...

Posting Permissions

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