[thesite] User page data

[rudy]

> So, anyone (I will be giving it a go -- don't know if anyone else is
trying)
> designing a user page concept needs to take into account:

well, isaac old pal, thanks for taking this on, but the design is pretty
straightforward, if you saw what i did at the last codefest
(http://evolt.org/member/rudy_limeback/2746/index.html)

the display of the information is really simple

please allow me to comment on your suggestions --

> - minimum data (along the lines of current user pages)
> - infinite data possible

the minimum data could be prompted by the form that lets people add/change
their info

i.e. if there's a box for phone number, the cold fusion could stuff that
into the table with "phone number" as the attribute, similarly for some of
the more common, or more useful, pre-defined attributes

otherwise, all the data would be be entered into field pairs --
attribute/value


> - all structured within sections, custom-named by users. (Any limit on
> section title length?)
>
> - each item can be sequenced (Any limit on item content length?)


let's jump right in, shall we?


first, we need a separate member table from the user table

CREATE TABLE MEMBER

  MEMBERID    NUMBER (8)    NOT NULL,
  CREATEDATE  DATE          DEFAULT SYSDATE NOT NULL,
  DATEMOD     DATE          DEFAULT sysdate NOT NULL,
  FIRSTNAME   VARCHAR2 (50)  NOT NULL,
  LASTNAME    VARCHAR2 (50)  NOT NULL,
  PRIV        NUMBER (1)    DEFAULT 2 NOT NULL,
  ACTIVE      NUMBER (1)    DEFAULT 1 NOT NULL,
  PRIMARY KEY ( MEMBERID ) ) ;

note that PRIV for all rows will be 2 or higher -- this will be the first
time we have seen PRIV=2 (member)

the only thing that's not really straightforward about this table is why we
need it, i.e., why not use the USERS table?

the answer is, because USERS records come and go (and sometimes dupes have
to be removed <grin>), and furthermore, there could be multiple legitimate
USERS per MEMBER (one for home, one for work, etc.)

so item #1 for the cold fusion coders is to create the administrative pages
that would allow for the add/change/delete of MEMBER records

item #1A is to allow USER records to FK back to the "parent" MEMBER -- the
FK for this is already in the USERS table

as to who gets to run these administrative pages, i.e. the privacy checks
on them, it is my opinion that any admin (priv>=3) should be able to,
*plus* each member (priv=2) should be able to run it for his or her own
record

i see dan entering member records every time somebody requests a m.e.o
account

i see members defining their own pages, linked to their own user records
(and the member pages would not work without the FK from a user record)


with me so far?


okay, next is the attributes we're going to store

CREATE TABLE ATTRIBUTE

  ATTRID    NUMBER (8)    NOT NULL,
  ATTRNAME  VARCHAR2 (50)  NOT NULL,
  PRIMARY KEY ( ATTRID ) ) ;

pretty simple, no?

the attributes will be "phone," "url," "cat," "sexual orientation," and/or
whatever else we may want to use and/or the members choose to add

is 50 characters big enough?  we can increase it

so item #2 for the cold fusion coders is to create the administrative
function that allows for the add/change/delete of ATTRIBUTE records

this administrative function -- note i did not say it had to be a separate
page, it could be built into the page where the member defines the values
(below) -- should be accessible to any member (priv >= 2)

oh, and delete *must* be defined with RESTRICT relational integrity so that
no member can blow away an attribute record that describes somebody else


now for the meat of the member pages structure

CREATE TABLE MEMBATTR

  MEMBATTRID  NUMBER (8)    NOT NULL,
  MEMBERID    NUMBER (8)    NOT NULL,
  ATTRID      NUMBER (8)    NOT NULL,
  SEQ         NUMBER (2)    NOT NULL,
  PRIV        NUMBER (1)    NOT NULL,
  ATTRVALUE   VARCHAR2 (255),
  REMARKS     VARCHAR2 (255),
  PRIMARY KEY ( MEMBATTRID ) ) ;


all the fields should be self-explanatory except ATTRVALUE and REMARKS

if not, holler

ATTRVALUE is where the actual value gets stored

so for my phone number, the MEMBATTR record would have MEMBERID pointing to
my MEMBER row, ATTRID pointing to the "Phone Number" ATTRIBUTE row, and
ATTRVALUE would be the actual digits

REMARKS is so that i can distinguish home numbers, work numbers, etc.

so item #3 for the cold fusion coders is the administrative page to
add/change/delete any member data

(is 255 characters enough?  we can increase it)

the structure is simple, but as suggested earlier, perhaps the code might
be a bit more complex, as we may want certain "standard" attributes
"hardcoded" into the form, so that people have some guidance in what to
enter for themselves (and any such fields left empty would simply not get
into the table)


and of course item #4 is the actual display of member data, which should
be, as i said at the outset, really really simple

the only thing i haven't settled on is whether ATTRIBUTE should have a SEQ
field, or whether the SEQ field in MEMBATTR governs all the values for that
member -- e.g. i might want my home number, then home address, then work
number, then work address, etc., which would not be possible if numbers and
address had a higher order sort sequence...  and also, who defines what the
ATTRIBUTE sequence values should be?


i apologize if this has been a more-or-less stream-of-consciousness
description of the underlying structure

if anyone has any questions, better get them on the table now, in case this
project actually gets tackled this weekend...


rudy