Uplift Project database design scheme

---

Relational database tables:

For Members only:

• Contacts - the connection hub for all other tables
• Profiles - related to contacts to contain profiles for all members
• Reviews - reviews by public & members
• Responses - responses to reviews by the reviewed members
• Projects - to support project seeds, management and documentation
• Matching - related to Projects to match resources with needs
• Feedback - member feedback on projects
• Collaborators - members collaborating on projects
• Tasks - project tasks, target and completion dates
• Needs - project needs
• Exchanges - resources on offer for exchange
• Spread - archive of Spread the Word message sent by UP
• Survey/voting management tables

Public:

Mature Projects - mature projects released to the public - possibly not a table, but a flag in the Project record

Database tables record fields

1st draft

Contacts

• contact ID - primary index - unique record number
• profile ID
• review ID
• Prefix
• First Name
• Middle Initial(s)
• Last Name
• Suffix
• Title
• Organization
• Org flag - if this is an organization contact with the (required) designated contact named above
• Status - codes: M = Member, P = Probation, X = Exiled, B = Banished, non-member
• treatment - codes: I = inactive-omitted from searches-as if deleted
• email
• doNotMail - flag to opt out of receiving mailings from other contacts except UT staff
• website - name
• URL - website URL
• phone - preferred phone #
• street
• line2
• city
• postCode
• box
• notes
• userName
• pw - accessible by Staff privileged or above
• reviews *
• privilege - codes: A = Admin - full RW access to all fields, S = Staff - full read access, limited write access, = limited read access
• dateAdded
• dateModded - date last modified

The entries into many fields in the tables below can be short descriptionss or links to long ones, as appropriate.

Profiles

• profile ID - primary index
• contact ID
• bio - brief bio
• interests
• skills
• quests
• activities
• education
• awards
• sharables
• notes
• dateAdded
• dateModded - date last modified

Reviews

• review ID - primary index
• contact ID
• posCount
• negCount
• responses ID - responses by contact ^Member arbitrators
• posCount
• negCount
• dateAdded
• dateModded - date last modified

Responses

• response ID - primary index
• review ID
• contact ID
• response - by contact
• arbitration - response by Member arbitrators
• dateAdded
• dateModded - date last modified

Projects

• project ID - primary index
• matching ID
• contact ID
• seed
• plan
• status
• feedback ID
• collaborators ID
• tasks ID
• cronolog
• dateAdded
• dateModded - date last modified

Matching

• record ID - primary index
• project ID
• needs ID
• exchange ID
• feedback ID
• dateAdded
• dateModded - date last modified

Feedback

• record ID - primary index
• project ID
• feedback
• dateAdded
• dateModded - date last modified

Collaborators

• record ID - primary index
• project ID
• contact ID
• role
• tasks - list of task ID's
• dateAdded
• dateModded - date last modified

Tasks

• task ID - primary index
• project ID
• task
• targDate - target completion date
• compDate - date completed
• notes
• dateAdded
• dateModded - date last modified

Needs

• needs ID - primary index
• project ID
• need
• exchange ID - of need fulfilled
• dateF - date fulfilled
• notes ID
• dateAdded
• dateModded - date last modified

Exchanges

• exchange ID - primary index
• exchange - resource offered to exchange for a need
• need ID - need addressed by this exchange
• notes ID
• dateAdded
• dateModded - date last modified

Spread

• spread ID - primary index
• contact ID - author
• message - Spread the Word message/li>
• approval - code: S = sent, W = needs work, X = rejected
• notes ID
• dateAdded
• dateModded - date last modified

Survey/voting management tables

I have already deployed a complete polling feature on a client's website using these tables plus associated data entry forms:

Polls

• PollID - primary index
• PollName
• PollType - E = election, I = Issue
• Description
• Instructions - to voters
• Eligibility - elegible member status codes or P = public
• VotesPerVoter - for multiple winners
• NumWinners - number of election vacancies to be filled, e.g. board members
• EndDate - last day of voting
• DateMod
• DateCr

Choices

• ChoiceID - primary index
• PollID
• First Name - or issue name
• Last Name - or issue tag line
• Suffix
• email
• Description - candidate or issue
• Votes - vote tally
• Date Mod
• Date Cr