VULAB-125 Resolved! - New Database Schema Outline

Wed Dec 17 02:24:40 UTC 2008

Hi Blake,

Took a really quick look at your db schema and didn't have any  
comments on that, but wanted to mention that in the past I've used  
dbDesigner (http://www.fabforce.net/dbdesigner4/) to model databases  
and had liked this tool. I'm not sure if it would provide all the  
details Peter suggest, but it (or it's successor--apparently it's been  
replaced by MySQL Workbench 5.0) may be worth checking out.

Cheers,
Allison

On Dec 16, 2008, at 9:00 AM, electBlake wrote:

>
> 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
>
> _______________________________________________________
> fluid-work mailing list - fluid-work at fluidproject.org
> To unsubscribe, change settings or access archives,
> see http://fluidproject.org/mailman/listinfo/fluid-work

Allison Bloodworth
Senior User Interaction Designer
Educational Technology Services
University of California, Berkeley
(415) 377-8243
abloodworth at berkeley.edu