Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / June 2007

Tip: Looking for answers? Try searching our database.

Design and Relationship question

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Scuda - 23 Jun 2007 02:37 GMT
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
Allen Browne - 23 Jun 2007 03:57 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi all,
>
[quoted text clipped - 44 lines]
>
> Steph
Scuda - 23 Jun 2007 05:16 GMT
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!

> 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
[quoted text clipped - 85 lines]
> >
> > Steph
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.