Relationship Question

[Mike]

This is a database for a fire department that i am trying
to work on, which is going to drive thier website. One of
the pages is to list all the Ex Chief's of the department
along with the year that they served as Chief. These are
the tables that i have below.

tblMembers
-memberid PK
-address
- and so on...

tblYear
-yearid PK
-year

tblExChief
-memberid PK
-yearid PK
-exchief(yes/no)

Is this the right way to handle this? Any help is greatly
appreciated.

Thanks,
MIke

 

[Jeff Boyce]

Mike

Seems like you might be doing more work than you need to. An "Ex-Chief"
seems like s/he might be very much like a "Current Chief" in terms of info
you want to keep. If you put the ?ExChief (Y/N) field in with your current
data, all the "Ex's" could have a "Yes" for that field. That way, you don't
need to maintain two separate tables of "Chief" info, and move folks from
one to the other.

Or have I misunderstood?

Good luck

Jeff Boyce
<Access MVP>

 

[Mike]

Jeff,

I understand what you mean. What's throwing me off is the
year. I need to hold that somewhere and i don't know if i
have to have a seperate table for that, and if so, how do
i relate everything together.

Unless i don't understand what you are saying??

Thanks, Mike

 

[Jeff Boyce]

Mike

May be time to take a step back. Relational database design (Access
included) focuses on identifying things about which you want to keep facts.
And the things are related to other things, hence "entity-relationship
diagrams" depict the categories (things/entities), their facts
(attributes/fields), and the relationships among them.

Your question, paraphrased, is "where should I stick this year?". So, which
entity does the year describe? Does it describe a person (not really!)? A
person has a name, a DOB, ...

Does it describe a job title? Maybe, getting closer, but no cigar. A job
title can be created on a date, be valid for a date range (from, to), have a
description, have a pay range, ...

(I'm still not clear, so this is a guess) Does your 'year' describe
something to do with a person holding a job title? If so, let me point out
that I have held numerous job titles, but 'year' does not describe any
fact/attribute about those jobs. Instead, StartDate and EndDate would be
more appropriate facts about the positions I've held.

Let me know if any of this made sense!

By the way, if your fieldname is actually "year", change it! Access treats
that word as reserved, and both you and Access will end up with headaches
trying to figure out what it means if you name a column that way.

Good luck!

Jeff Boyce
<Access MVP>