Umpire organization database design

[johnbennett0310]

I am trying to design a database application for the local umpire groups to
which I belong. I need to keep track of personal information, meetings
attended each year, test scores each year, fees paid each year, ratings for
each year. I would also like to include a picture of the umpire.

Has there been such a database created by anyone? I checked but could not
find anything. Thanks.

 

[tina]

all of that is certainly do-able in Access. to design your own database,
you'll need to start by learning the basics of relational design principles.
for more information, and more tips on the basics of building a database,
see http://home.att.net/~california.db/tips.html.

hth

 

[Fred]

So far you have only asked for an already-made database, not for help or
guidance. If you can't find an already-made one, you might want to repost
and ask for help.

Fred

 

[johnbennett0310]

Thanks for the replies. I could not find an already-made database. I have
experience with Access and have created a number of databases already, and I
understand the basics of database design. But I need suggestions on creating
some of the tables for this umpire database.

The "major" table will be the personal information table, with a unique ID
for each umpire, but I do not want it to have every possible field of
information. Instead I would like to have sub-tables: one for attendance,
one for test scores, one for fees payed, etc. These will all have the umpire
ID so I can set up a relationship to the personal information table. Is that
the proper way to design this database?

I do not know the best way to set up the sub-tables. Is it one record for
each meeting and then many fields with the umpire IDs of who attended? But
that could be 100s of fields. Do I set up a table with a composite key with
meeting number and umpire ID? But that would be a table of 100s of records
with just a yes or no in the only other field in the table.

I have the same concept/questions for the other sub-tables. One record for
each test? etc.

This database must be designed to cover all future years, so I need
information like the attendance at each of the 5 or 10 meetings every year
for many years into the future.

Thanks, any help is appreciated.

 

[Steve]

Hello John,

I suggest these tables .....

TblUmpire
UmpireID
<appropriate personnal information fields>
UmpirePhotoPath Path to photo stored in separate folder

TblMeeting
MeetingID
MeetingDate
Meetingcation
<other meeting descriptive fields>

TblMeetingAttendance
MeetingAttendanceID
MeetingID
UmpireID

TblTest
TestID
TestDate
PerfectScoreValue

TblUmpireTestScore
UmpireTestScoreID
TestID
UmpireID
UmpireTestScore

TblUmpireFee
UmpireFeeID
UmpireFeeYear
UmpireFee
UmpireFeeDueDate

TblUmpireFeePaid
UmpireFeePaidID
UmpireFeeID
UmpireID
UmpireFeePaidDate

TblUmpireRating
UmpireRatingID
UmpireRating

TblUmpireAnnualRating
UmpireAnnualRatingID
UmpireAnnualRatingYear
UmpireID
UmpireRatingID


Steve
(e-mail address removed)

 

[Fred]

I think that your thought process is sound, including on what you already
decided and where you decided to stop and ask.

Starting with the "full strength" case, where there is substantial
attendence, fee and test score data for a typical umpire, and you've decided
that you need to record all of that.

In that case, your subtables have a record for each instance of an ump doing
one of those things. Specifically, your "attendence" table will have a
record for each instance of an ump attending or missing a game, your test
scores table will have a record for each instance of an ump taking a test,
and your fees table will have a record for each instance of a rep paying a
fee. All linked to you main table as you described. This design should
support recording everything that you described, and printing & summarizing
in almost any imaginable way.

If the answers to any of the above questions points towards less data or
less recording, you might make some practical decisions which scale back from
the above. For example, if you just need a tally of fees paid or games
missed / attended, you could skip the attached table and just put in a
"tally" field in your main table. Or, if there were only a few instances
of those items (for example, only one test per year) you could back away from
a fully normalized design and just put in test score fields for the next 5
years in the main table

Hope this helps a little.

Sincerely,

Fred

 

[tina]

I understand the basics of database design.

the questions you're asking are basic relational design questions. that
suggests that you need to read up/more on relational design principles
before you proceed. from the information you've posted, you're planning to
store a lot of data over a long period of time - taking the time now to make
sure you understand how to optimize the base database structures
(tables/relationships) will pay off 1,000-fold over the long haul.

hth