[playah]

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

[r937]

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

[playah]

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.

[healdem]

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

[playah]

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.

[andrewst]

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
...