Design and Relationship question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

Have a question here. I am creating a new DB and want to get the design
right first.

Concept: I work for the US Coast Guard. Small Boat Stations need to call us
up when they need a "waiver" for problems with boats. So, each Station has 3
unique areas that I am concerned with. Each has a:

StationName
OinC (Officer in Charge)
Boats (average of 4 boats per station)

The OinC changes every 3 years or so, boats not that often, StationName
rarely.

My initial thoughts are to have 4 tables as such:

tblMaster
MasterID (PK)
fkBoatID
fkStationID
fkOinCID

tblBoats
BoatID (PK)
Boat

tblOinC
COOinCID (PK)
COOinC

tblStations
StationID (PK)
Station

Then relate the PK's to the FK's in the Master Table.

What another of my end goals is to Cascade some combo boxes on my forms:

Dropdown with BoatNumber, when selected fills in the STATION and the OinC
combo boxes for me.

Sorry for the long post, please let me know if I am way off base!

Steph
 
The record you have in tblMaster ties a boat + station + officer together.
Are the boats actually tied to the officer? If not, you may be better
dumping tblMaster. Replace it with one table that assigns boats to a
station, and another that assigned the officer to the station.

If an officer or boat could be reassigned to another station, but you need
to keep track of when they were at the previous station, you might add a
couple of date fields for the assignment range, so the tables would be like
this:
tblStationBoat
StationBoatID (pk)
fkStationID
fkBoatID
StartDate Date/Time. When boat was assigned to station.
EndDate Date/Time. Blank until de-/re-assigned.
and similarly for tblStationOinC.

An alternative idea, would be to put the officers and boats into one table
(perhaps with other types of resources that get assigned to stations.) This
tblResource:
ResourceID (pk)
fkResourceTypeID (boat, officer, ...)
ResourceName
so that you only need one tblStationResource table. Depending on other
factors, this may or may not be a good idea.

Now, back to the first question: If an officer is assigned to a boat at a
station, your tblMaster is the right approach, but you may wish to add the
StartDate and EndDate of the assignment (unless you only keep the current
assignments, and don't need to know about past assignments.)

As to your goal of cascading combos, the structure you have allows an
officer to be assigned to multiple boats and stations (unless mark the
fields required, and use a unique index on the combination of the 3 fields.)
Therefore the cascading combos would not have a unique combination to
assign. For general help on cascading combos, see:
http://www.mvps.org/access/forms/frm0028.htm

Ultimately, you are closer to the data to choose the most appropriate
approach, but hopefully that gives you some alternatives to consider.
 
Hi Allen, thanks that is some good stuff. I will defintely use your advice,
especially:

quote: Replace it with one table that assigns boats to a
station, and another that assigned the officer to the station.

Thanks again!
 
Back
Top