Groups and positions

TODO

SQL tables

The general relationship between the tables is:

                         position_relations
                               |   |
members - position_holders - positions - groups
                                 |
                         position_permissions
                                 |
                            permissions

We have tried to make this as general as possible. For example:

• All permissions come from positions, whereas the legacy database allowed members to have permissions directly
• All memberships in groups come from particular positions in the group
• House memberships are represented as held positions, e.g. (group_name, pos_name) = ("Avery", "Full Member")
• The position_relations table allows for "indirect" positions, e.g. the ASCIT president position also gives the administration position for all ug-* groups
• position_holders stores a historical record of all positions; usually current_position_holders will be used instead to get currently held positions
• groups is used for both campus positions and sending out emails, whereas the legacy database distinguished between "organizations" and "newsgroups"

groups

Column	Type	Comments
group_id	INT	PK
group_name	VARCHAR(64)	Distinct
group_desc	VARCHAR(255)	Optional
type	VARCHAR(255)	Currently, we have "house", "committee", and "ug-auto"
newsgroups	BOOLEAN	Can emails be sent to this group?
anyone_can_send	BOOLEAN	Can non-members send to this group? Currently only ASCIT allows this
visible	BOOLEAN	Can non-members see that this group exists?

positions

Column	Type	Comments
group_id	INT	Group this position belongs to
pos_id	INT	PK
pos_name	VARCHAR(64)	Unique per group
send	BOOLEAN	Whether this position can send emails to group
control	BOOLEAN	Whether this position has admin control over group (e.g. adding/removing positions)
receive	BOOLEAN	Whether this position receives emails sent to this group

TODO