VULAB-125 Resolved! - New Database Schema Outline

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

Agreed. http://issues.fluidproject.org/browse/VULAB-131

> - 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?

most definitely.

>
> - 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  
team.
http://issues.fluidproject.org/browse/VULAB-133

>
> Some stuff to chew on

with some great nutritional value :)

> Peter
>
> 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