list within a field

[Guest]

I'm trying to build a database on city councillors, wards, and their actions.
I have a table COUNCILORS, a table WARDS, and a table COMMITEES. I'm trying
to figure out the best way to list the committee members (who are all
councilors).

I tried this for fields int he committee table: ID, NAME, CHAIR, VICECHAIR
-- the problem is with the members. Some committees can have many many
members, and I want to avoid having fields such as MEMBER_1, MEMBER_2,
MEMBER_3, etc...can't i just list the various councilors who are members for
a committee in one cell?

What should I do?

JG

 

[Tim Ferguson]

I tried this for fields int he committee table: ID, NAME, CHAIR,
VICECHAIR -- the problem is with the members. Some committees can
have many many members, and I want to avoid having fields such as
MEMBER_1, MEMBER_2, MEMBER_3, etc...can't i just list the various
councilors who are members for a committee in one cell?

This is a bog-standard many to many relationship. Just put it all in a
table of Placements looking like

CommitteeCode ' references Committees
CouncillorID ' references Councillors
Role ' references RoleTypes: Chair, Sec, Vice, Ordinary etc
DateOn
DateOff
BungsPaid ' etc


Make up the PK on the combination of (CommitteeCode, CouncillorID) to
avoid double-counting. You can use some fancy database coding to avoid
having more than one Chair or Vice etc in a committee; not easy in Access
although it's readily do-able in a real database platform.

Hope that helps


Tim F

 

[Jamie Collins]

This is a bog-standard many to many relationship. Just put it all in a
table of Placements looking like

CommitteeCode ' references Committees
CouncillorID ' references Councillors
Role ' references RoleTypes: Chair, Sec, Vice, Ordinary etc
DateOn
DateOff
BungsPaid ' etc

You can use some fancy database coding to avoid
having more than one Chair or Vice etc in a committee; not easy in Access
although it's readily do-able in a real database platform.

As you point out, a generic table of placements rather than specific
relationships tables makes the constraints harder to define. If the
chair relationship is 1:1 and the members relationship is 1:M then
having separate tables for each relationship will make things a bit be
easier.

FWIW I think attributes in the committee tables, as the OP has it,
work well where the rule is e.g. a committee must have at least one
chair and at most one chair -- exactly one chair -- because using a
relationship table makes the 1:1 relationship more like 0..1:1, the
'fancy database coding' required to ensure a referencing table has at
least one row being truly awful in Access/Jet.

If the roles (members, chairs, etc) are in different tables, they
could all be brought together using UNION in a VIEW. I'd be tempted by
this approach in a 'more capable' DBMS, where I could use WITH CHECK
OPTION to control the constraints and perhaps INSTEAD OF triggers to
make the VIEWs updatable.

All that said, although the constraints are more complex with a single
Placements table, they are not too bad in Access because you can use
field-level and record-level Validation Rules e.g. say you could have
(up to) one Chair, one Sec, two Vice and 20 Ordinary:

CREATE TABLE Placements (
CommitteeCode INTEGER NOT NULL REFERENCES Committees (CommitteeCode),
CouncillorID INTEGER NOT NULL REFERENCES Councillors (CouncillorID),
Role VARCHAR(8) NOT NULL,
CHECK (Role IN ('Chair', 'Sec', 'Vice', 'Ordinary')),
seq INTEGER NOT NULL,
UNIQUE (CommitteeCode, CouncillorID),
UNIQUE (CommitteeCode, Role, seq),
CHECK ('T' = SWITCH (
Role IN ('Chair', 'Sec'), IIF(seq = 1, 'T', 'F'),
Role = 'Vice', IIF(seq BETWEEN 1 AND 2, 'T', 'F'),
Role = 'Ordinary', IIF(seq BETWEEN 1 AND 20, 'T', 'F')
))
)
;

I've posted as CHECK constraints for clarity but the examples here can
be replaced with Validation Rules

BTW the limits (1, 2, 20 etc) don't have to be hard wired into the
schema in this way and can be different for all committees keeping the
same king of design (DRI and Validation Rules); for an example see:

http://groups.google.com/group/microsoft.public.access/msg/1d4430dc8b50ba8f

Make up the PK on the combination of (CommitteeCode, CouncillorID) to
avoid double-counting.

Modelling the relationship table as periods (DateOn, DateOff) makes
things considerably more complex and invalidates your suggestion for
PK i.e. you'd instead need a *sequenced* primary key to allow a
councillor to be a member of the same committee during different
historical periods, no overlapping periods, etc. The combination of a
generic Placements table and valid-time states would make for a
*really* complex table!

Jamie.

--

 

[Guest]

This rapidly spiraled beyond my comprehension...

I did what Tim recommended and it works, I guess. The ultimate goal of my
DB is to track councillor voting records. I would like to, in the end, have
a form that shows councillor data (name, contact info, committee roles), with
multiple subforms in a tabbed box below. One subform would show ward data
for that particular council, another might show voting records in council.

So far, i have several tables: Councillors, Wards, Committees, and now also
Committee_Membership, and Roles. Councillors is linked to Wards on ID (i
used the same numbers for wards and for councillors). Committees, Roles, and
Councillors are linked to Committee_Membership on (respectively)
committee_id, councillor_id, and role_id.

There are 23 councillors and 1 mayor. They have two principal duties: to
sit on committees and to vote in council. My primary goal is to track voting
in council, but may need to keep records of committee actions as well. Thus,
my earlier question about storing committee membership.

There is only one councillor per ward. There is one chair, one vice chair,
and up to nine members per committee. In light of this info, is Tim's method
still preferred?

Council meets twice monthly and votes on a number of issues. I figured to
make the voting table, i would have an 'action' field (storing the name of
the issue), and fields for each councillor (only 23). each record would
store the councillors action on that item (yea, nay, absent, abstain).
Another separate linked table would rank the issue on its environmental
credentials. This way, i can indirectly score a councilors environmental
record, no? Is this the right way to do this?

I understand this is a complicated issue, but would greatly appreciate any
help on sorting out my problems. Thanks again to both who have replied...

 

[Tim Ferguson]

Modelling the relationship table as periods (DateOn, DateOff) makes
things considerably more complex and invalidates your suggestion for
PK i.e. you'd instead need a *sequenced* primary key to allow a
councillor to be a member of the same committee during different
historical periods, no overlapping periods, etc. The combination of a
generic Placements table and valid-time states would make for a
*really* complex table!

Yes, good catch; comes of trying to put too much into one post and not
thinking it all through in detail.

Thanks for working through the other points -- I felt you would not be
far behind!!


B Wishes


Tim F

 

[Tim Ferguson]

Please keep one thread in one thread. By chance, this one ended up next
to its ancestor in my newsreader, but it usually won't.

So far, i have several tables: Councillors, Wards, Committees, and now also
Committee_Membership, and Roles. Councillors is linked to Wards on ID (i
used the same numbers for wards and for councillors). Committees, Roles, and
Councillors are linked to Committee_Membership on (respectively)
committee_id, councillor_id, and role_id.

Council meets twice monthly and votes on a number of issues.

I see a table of Issues (DateOfVote, GreenPotential, WhoProposed, etc)
and then a table of Votes (CouncillorID, IssueNumber, Action, etc). This
should give you very direct access to all the GreenPotential values
voted for and agains by any particular councillor.

I understand this is a complicated issue, but would greatly appreciate any
help on sorting out my problems. Thanks again to both who have replied...

The main complicating factor that you have not mentioned is the historic
dimension. If you need to track changing personnel in various chairs and
committee membership it becomes much more complex; avoiding
duplications and so on. You don't want Bloggs being a councillor from
Jun 2001 to Jan 2007 and from Oct 2006 onwards... And to ensure that
there was only one chair of each committee at any point in time. And
that nobody was serving on a committee at a time when he wasn't on the
council. And that he wasn't voting...

I am not sure that modelling that level of complexity is suitable for a
NG support -- although Jamie might be up for it and I'd love to eavesdrop!

All the best


Tim F

 

[Guest]

Tim, thanks again for the assistance, it is greatly appreciated.

I envisioned the same tables for the votes, I'm glad that you've confirmed
that is the right approach.

As for the historical dimension, I dont believe it is that important in my
circumstance. The council is small (Ottawa, Ontario has only 23
councillors), and they've just been elected in the last couple months to what
i believe is a 4 year term. As this is a volunteer project, I dont expect to
still be working on it when they go for reelection, although if i am, its not
much just to substitute the councillor record based on the ward number.
Also, the vote/issue tables would probably just be began anew...

Thanks again,
James