[SUCS Devel] Data held on membership

[Chris Jones]

On 23 Jul 2007, at 11:17, Denis Walker wrote:

> Another issue that needs to be addressed regarding membership data  
> is where it is to be stored.
>
> Currently, it is held in the old "sucs" postgres database, which  
> I'd hope we can make redundant in the medium term - it was the one  
> used by the old website and contains various unused tables relating  
> to the old site.
>
> There is the possibility of holding it in the "sucssite" db, but  
> I'm not personally keen on that idea as I think membership data  
> should be stored separately from the website.
>
> Personally, I think the most logical place would be in the LDAP  
> database, which would mean that all member data is held in the one  
> place and would make maintaining it much easier. However, I don't  
> know how easy it would be to add fields to do this.
>
> So I would suggest LDAP for preference, but if that proves  
> impractical, then a new dedicated postgres db (called "members"?).

Whilst storing everything in LDAP would be nice, it makes it hard to  
limit access to data in simple yet a granular fashion, thus sticking  
with the database is probably the best plan.

I propose the following schema...

CREATE TABLE member_type (
     id serial NOT NULL PRIMARY KEY,
     typename text NOT NULL,
     blurb text NOT NULL
);

To follow the current convention of:
1       Student
2       Society
3       Honorary
4       Life
5       Associate

CREATE TABLE members (
	uid integer NOT NULL PRIMARY KEY,
	username text NOT NULL,
	realname text NOT NULL,
	email text,
	address text,
	phone text,
	sid integer,
	type integer REFERENCES member_type NOT NULL,
	paid text NOT NULL,
	comments text,
	lastupdate timestamp(0) DEFAULT now() NOT NULL,
	lastedit integer REFERENCES members NOT NULL
);
	
CREATE TABLE guestnet (
	mac text NOT NULL UNIQUE,
	uid integer REFERENCES members NOT NULL
);

CREATE TABLE doorcards (
	cardnumber integer NOT NULL UNIQUE,
	uid integer REFERENCES members NOT NULL
);

Out of personal preference I would also stick with keeping this data  
within the 'sucs' database.

> A particular reason for my interest is that I've just started work  
> on a member "Options" page for the website (http://beta.sucs.org/ 
> Tools/Options) which is intended to be able to edit some of this  
> information. I'm intending that any changes will require the user's  
> current password to be entered, to avoid them forgetting they've  
> left themselves logged into the site and someone else making  
> changes. Perhaps some changes might also require admin approval  
> before being applied?

Excellent!

I don't think any changes to user provided data would require admin  
approval, we have a nightly snapshot of that data if we ever need to  
look back at old data.

Graham was working on a password changing tool, I don't know how far  
he got though...


> Of course, another reason is that we really ought to get all the  
> member handling stuff moved from old.sucs.org before the beginning  
> of the new academic year.

At the very least moving the signup system to smarty (and the  
associated check/rewrite of the validation and user-add stuff) would  
be good.

--
Chris Jones, SUCS Admin
http://sucs.org