VULAB-125 Resolved! - New Database Schema Outline
electblake at gmail.com
Tue Dec 16 17:00:51 UTC 2008
On 16-Dec-08, at 12:30 AM, Peter Rowley wrote:
> Hi Blake and all,
> Blake and I will be discussing the schema tomorrow in Connect at
> 1:30, but here are some initial comments.
> - In projects, is author_id meant to match user_id in users?
yes it is, it would probably make sense to rename that field to
user_id or something more directly related to the user_id ?
> - It'd be helpful to have datatypes for the fields ("columns" in
> database-speak), like number, date, string (with max length), or
> boolean and brief phrases that describe what they mean
> - Similarly, it's important to have a brief phrase to describe the
> role of each table
Roger that. http://issues.fluidproject.org/browse/VULAB-132
> - I guess pre_survey_id and post_survey_id match survey_id in surveys?
> - What's response_limit in projects?
When we were originally planning vulab we wanted to have a system that
would collect a specific amount of responses and then automatically
set itself to "complete". This would be the control variable for that,
and I imagine this would be optional for now as we will have many
important matters to attend to before this feature. I wanted to
include it into the database nonetheless.
> - What's competestamp in projects?
the date that a project status was marked as "complete"
> - What's the meaning of a row in project_access?
each row grants access project. a row can be a single user, a group,
or a specific role.
> - Is longstamp in users the last login date/time, or some other?
typo :) yes, it should be logstamp which would be a mysql formatted
timestamp of when the user last logged in.
> - What is client_details for? status_codes?
- client details is for the capture of the browser and client
information of the tester at the time of the survey. This table I
imagine will be fexible as we discover new types of data to collect.
(we've even discovered 2 ones from the list)
- status codes are the various status' that a project can go through.
they are the stages of a project's life cycle (maybe we want the name
to reflect that rather then status?). I imagined the status' would
reflect what we have right now. draft, active, complete. Maybe we
should add a status for archived? or "deleted" ?
> - In surveys, you say you have an array of questions, but then it
> looks like survey_questions is a table that has the questions in a
> survey (and I think you need a survey_question_id to be a primary
> key for that)
yes, I should have taken that out because i DID create that
relationship table to replace that and grant me more freedom for data
stored in regards to the type of relationship.
> - same issues for questions with question_types
> - What does video_id in responses mean?
video_id is the unique session id that we use to tie a video to
rascal. Every video recording is given a session_id which is how we
can link to the .avi directly :)
> - I think we need to work through what value means in responses; in
> particular, what does it mean if it's different from the
> corresponding value in choices
I imagined we'd have value as something flexible and generically it
won't make sense. I am designing the system to use these generic
tables and the models can parse and understand the information for
each question. (choices, and values) I can explain this more with the
sample workflow we're doing with: http://issues.fluidproject.org/browse/VULAB-133
> In general, I expect that you'd find it useful to create some sample
> tables for two projects, each with one survey with two questions
> each with two choices (not sure what groups are, so that would
> factor in) and then the experiment is run with two users. That
> would help people understand some of the tables like client_details
> and status_codes
Now THATS a good idea :) A nice narrative to go with this would be
very useful to anyone wanting to jump onboard the VULab development
> Some stuff to chew on
with some great nutritional value :)
> On Dec 15, 2008, at 11:11 AM, electBlake wrote:
>> Hey Guys,
>> Just wanted to keep you up to date with the cool happenings of
>> VULab Web :)
>> With the holidays coming up, I'm on a blitz to have all of the
>> planning documentation for this framework switch done before we
>> leave :)
>> Here is the start: a database schema - http://issues.fluidproject.org/browse/VULAB-125
>> which refers to wiki post here: http://wiki.fluidproject.org/display/fluid/VULab+Database+Schema+Planning
>> Comments & Questions are always welcome!
>> - Blake
> fluid-work mailing list - fluid-work at fluidproject.org
> To unsubscribe, change settings or access archives,
> see http://fluidproject.org/mailman/listinfo/fluid-work
More information about the fluid-work