Is there a better way of doing this?

[MichaelSmith]

Hi,

Quick question regarding table structures, fields and relationships.

I'm trying to record information on historic ships for a research project. I've got a main table called tblShips which contains the name of each ship, a linked table called tblVoyages which records information on the different voyages the ships made. I'm also capturing information on ship captains (there could be up to three different captains per voyage) and ship owners (up to 16 per voyage - part ownership was common and shares were split by 16ths).

I've created three fields in tblVoyages - Captain1-3. I've linked these to tblCaptains via Lookups. To record the up to 16 Owners am I going to have to create a further 16 Lookups/Relationships or is there a better way of going about it?

Image for reference:

Thanks!

Mike

 

[plog]

Yes a junction table (http://en.wikipedia.org/wiki/Junction_table), it helps to define many - to - many relationships.


I'll explain how setting this up for captains will work, but the owners is the same principle. What you do is remove all Captain data from tblVoyages. Then you set up a new table with at least 2 fields:

VoyageCaptains
VoyageID, CaptainID

Then for every captain you put the VoyageID and corresponding CaptainID in VoyageCaptains. Now you can have as many captains as you want per voyage.

You can also add more fields to VoyageCaptains--dates for when they were captain of that voyage, notes, etc.

 

[MichaelSmith]

Ahhh, I see. I was being fairly dim about it all.

There's a many to many relationship between Ships and Captains and I was using Voyages as the junction table for that, but I can also put a junction table between Voyages and Captains.

Question though:

So currently the many to many link I have is

Ships--Voyages(Junction)--Captains

It'll change to

Ships--Voyages(Junction)--VoyagesCaptains(Junction)--Captains

Does this maintain the same Ships-Captains many to many link?

 

[plog]

Yes it does. Once you have your structure set up you can load some dummy data into it to test this.

 

[MichaelSmith]

That's perfect, thank you for the help. You've just made this a hell of a lot less time consuming/complicated!

 

[MichaelSmith]

Ok, thanks to lots of help from the forum I think I've got my final database structure all laid out.

I still need to play around with some of the fields in some of the tables to make sure I'm capturing all of the data I need but if anyone has any suggestions/improvements/tweaks for the structure I'd love to hear them.

 

[namliam]

Either way datefrom and dateto would typicaly be columsn of the junction table, since the captain named Namliam on the boat called Access was captain from - to
then became captain of the Excel from - to
Then became captain of the Word from - to

In your design you would have to have Namliam, DOB, DOD, etc multiple times just to be able to record what boats he captained.

Also can a captain be owner and/or researcher as well? If so you may want to make that into one table "People" and have the Junction table define their "role"

Edit: and reading your OP more closely, if there are 16 (or x) parts to the ownership that can change hands "mid voyage" as well....
Perhaps you need to create a "part number" column and a "from" and "to" column there as well.

 

[MichaelSmith]

Hi Namliam,

A ship could have had up to three captains on any one voyage (they tended to die fairly regularly). Ships were owned by up to 16 owners for any single voyage and ownership could change from voyage to voyage. A captain could also be a part owner of the ship. Captains and owners can't be researchers and vice versa a researcher can't be a captain or an owner.

I'll move the datefrom, dateto in the captains table over to jctVoyagesCaptains.

Thanks for your feedback, any extra feedback is always appreciated.

 

[namliam]

I editted my earlier post slightly, just making sure you see it after already responding.

If captains can be owners then perhaps you need to merge the owner and captain tables to prevent data from being stored 2 times.

 

[MichaelSmith]

Thanks, going over your edited post:

Ownership doesn't change mid-way through a voyage, it only changes once a voyage is completed.

You make a good point about about merging owners/captains to prevent repeated data - I'll have a think of the best way to go about it since I need to capture slightly different background data for owners and captains.