search

publiclab / wherewebreathe

wherewebreathe.org
2 stars 7 forks source link

Database Design #19

Closed mmnoo closed 10 years ago

mmnoo commented 10 years ago

So far, I am thinking along the lines of this for the database:

Thoughts? @jywarren

(its easier to look at in something like gedit...)

Housing Unit
{
  _id: String, //HUD/VIN ** there was a mention of the existence of formatting notes. could those be linked to?
  make: String,
  model: String,
  dmfg: Date, //date of manufacture
  desc: String, // Description - "Which of the following describes this housing unit?"
  location: Object{
            city: String,
            state: String,
            zip: String
            }           
}

User
// have yet to approach login component in-depth. This part (value types?) could likely change when I get there.
{
  _id: Objectid, //so exported data is less able to be tied to individuals
  n: String, //user handle (unique)
  d: Date, //date joined (do we care? dont need this if use Objectid.getTimestamp())
  p: String, //password
  hId: String //housing unit id
}

Answers
{
  qId: Objectid, //references quesiton id
  set: String, //E.g. "housing-unit". To make the db more poeple-friendly, export-friendly, however this is redundant, and can be queried from qID... probably waste of server resources...
  uId: Objectid, //references the user id of the survey-taker. Maybe this shoule be obscured for export?
  d: Date, //date answered
  a: Object // answer. Is Object to accomodate questions that have more info. E.g. about symptom onset and abatement, etc. 
}

Questions
{
  _id: Objectid,
  order: Number, //order to ask questions, (can be edited later and not break links) 
  type: String, //  type of question to direct which html object to use E.g. "mc-drop" to use a drop down select 
  set: String, // E.g. "health-issues".
  question: String, // E.g. "What make is your housing unit?" 
  label : String,// E.g. "Click the drop down list to choose a make"
  answers: Array, // depending on type of question (e.g. radio, dropdown), a list of possible answers will be supplied here. Potentially, if 'type' indicates that the question has further conditional questions the array will be of object like below, if not the array will be of strings (to save space? does this matter on less than 100 questions?) Perhaps the existence of the nextQ will be the indicator of further questions.        
        [{
          text: "cats",
          nextQ: Number //refers to number of next conditional question (probably start conditional question numbering in 1000s
        }]
  submit: String // potentially to differentiate database collection to save to. E.g. Answers vs. Housing. (could also use set value above
}
jywarren commented 10 years ago

This is looking good. Do you like using a visual representation? Perhaps a Google Drawing to show the interconnections?

mmnoo commented 10 years ago 
User{
  ... 
  public_:  String //yes, no, 
  share: String //wwb,?
}
User{
  ... 
  visibility:  String //public or wwb only 
}
{
  qId: Objectid, //references question id
  set: String, //E.g. "housing-unit". To make the db more people-friendly, export-friendly, however this is redundant, and can be queried from qID... probably waste of server resources...
  graph_type: String,
  answers: Array of Objects[{
            uId: Objectid, //references the user id of the survey-taker. Maybe this should be obscured for export?
            d: Date, //date answered
            a: Object // answer. Is Object to accommodate questions that have more info. E.g. about symptom onset and abatement, etc. 
            }, {...}, {...}]
}
jywarren commented 10 years ago

A prompt is the grey text that appears in a text field, suggesting what the user might enter, like "you@email.com" or "+1 ### ### ####" or something.

I agree on question intros for the MVP. Let's keep it simple since there are few sets for now. Fewer tables is usually easier to work with, IMO.

On Thu, Jul 3, 2014 at 6:33 PM, Melissa notifications@github.com wrote:

User{ ... public_: String //yes, no, share: String //wwb,? }

  • actually I am not so clear on the particulars of what those two settings mean. I imagine they could be wrapped up into one toggle like "any one can view your data" or "only the WWB team can view your data". Then the key could just be

User{ ... visibility: String //public or wwb only }

  • Good point on gType.
  • Question.graph_type probably works better seperated.
  • Your point about graph_type gets me thinking more about embedding the answers to save space and minimize queries. Apparently for each document, Mongo runs a separate query, so if we do something like below it is only one query instead multiple ones. Something like:

{ qId: Objectid, //references question id set: String, //E.g. "housing-unit". To make the db more people-friendly, export-friendly, however this is redundant, and can be queried from qID... probably waste of server resources... graph_type: String, answers: Array of Objects[{ uId: Objectid, //references the user id of the survey-taker. Maybe this should be obscured for export? d: Date, //date answered a: Object // answer. Is Object to accommodate questions that have more info. E.g. about symptom onset and abatement, etc. }, {...}, {...}] }

  • A header description. E.g., "The following questions are about your housing unit...", for QuestionSet could be set in the app code based on the QuestionSet's value or provided by its own lookup table. If we set it in the questions table for each question, I worry that the description for some questions will get edited by a maintainer, and others not, and we will end up with inconsistencies. For the MVP, I’m thinking I'd like to hard code it into the application, and then depending on if this phase has extra time, port it to a lookup table. Thoughts?
  • I'm not sure what you mean about adding "prompt" to Answer.

— Reply to this email directly or view it on GitHub https://github.com/publiclab/wherewebreathe/issues/19#issuecomment-47953301 .

mmnoo commented 10 years ago

Prompt: gotcha. I misread 'Answers' and thought of the Answers table. That makes sense. :)

shapironick commented 10 years ago

I'm a little behind on this but regarding M's question about VIN + HUD number formatting in the initial post:

VIN vs HUD is mentioned in issue 7 and there is some help text here: http://publiclab.org/wiki/wherewebreathe#Am+I+looking+for+a+VIN+or+a+HUD+number ?

VIN numbers are 17-characters long and does not include the letters I (i), O (o), or Q (q) (to avoid confusion with numerals 1 and 0). so if i, o, or q do get entered into a VIN field we might want some help text to come up asking them to check their records again.

HUD numbers are typically composed of three letters followed by six or seven numbers--interestingly HUD regulates manufactured homes homes in a much looser way than DOT regulates vehicles used as homes.

On Thu, Jul 3, 2014 at 11:40 PM, Melissa notifications@github.com wrote:

Prompt: gotcha. I misread 'Answers' and thought of the Answers table. This makes more sense. :)

— Reply to this email directly or view it on GitHub https://github.com/publiclab/wherewebreathe/issues/19#issuecomment-47992491 .

jywarren commented 10 years ago

I think it's reasonable to store HUD and VIN #s in the database as either strings delimited by "-" or as nested arrays, but i guess strings may be better since they can easily be stored in a traditional SQL database. We just say "23-2352-23525".split('-') or whatever the Javascript equiv is.

On Fri, Jul 4, 2014 at 2:10 PM, shapironick notifications@github.com wrote:

I'm a little behind on this but regarding M's question about VIN + HUD number formatting in the initial post:

VIN vs HUD is mentioned in issue 7 and there is some help text here:

http://publiclab.org/wiki/wherewebreathe#Am+I+looking+for+a+VIN+or+a+HUD+number ?

VIN numbers are 17-characters long and does not include the letters I (i), O (o), or Q (q) (to avoid confusion with numerals 1 and 0). so if i, o, or q do get entered into a VIN field we might want some help text to come up asking them to check their records again.

HUD numbers are typically composed of three letters followed by six or seven numbers--interestingly HUD regulates manufactured homes homes in a much looser way than DOT regulates vehicles used as homes.

On Thu, Jul 3, 2014 at 11:40 PM, Melissa notifications@github.com wrote:

Prompt: gotcha. I misread 'Answers' and thought of the Answers table. This makes more sense. :)

— Reply to this email directly or view it on GitHub < https://github.com/publiclab/wherewebreathe/issues/19#issuecomment-47992491>

.

— Reply to this email directly or view it on GitHub https://github.com/publiclab/wherewebreathe/issues/19#issuecomment-48036436 .

mmnoo commented 10 years ago

@jywarren For VIN/HUD, what are your intentions with the extra dashes?

Looking at the pictures here, the original format doesnt contain dashes. Could we not just store VIN/HUD as a simple string, sans dashes and have the input be one text box?

shapironick commented 10 years ago

A simple sting sounds good for vin numbers. You're right, there are not natural breaks in VIN numbers, although there is a space after the first three characters of HUD numbers.

jywarren commented 10 years ago

A few followup issues:

Otherwise @mmmelissa how are we doing on database design, do you think we can close this issue for now, or do we need to discuss comments, or other things?

jywarren commented 10 years ago

Sorry re: dashes response; I just have had trouble signing up to test because i can't easily fake a VIN or HUD # - but i guess that's a feature. I just don't want people to get frustrated or stuck on that step but we can open a new issue for that if it becomes a problem, during user testing.

mmnoo commented 10 years ago

Would it be ok to allow spaces and dashes at any part of the number, and then just strip them server side and store only the numbers? That shouldnt be too hard.

As it is now, the placeholder should say 'please remove spaces and dashes', it doesnt accept dashes, but I have forgotten to communicate that.

mmnoo commented 10 years ago

Re: "repeatable"... im wondering if it makes sense to store follow up repeated questions separately

Just thoughts, it would be easier to say when actually coding it.

jywarren commented 10 years ago

That's very well thought through and I prefer your solution to "repeatable". Maybe a field "repeats" with a question ID to show relationships between questions (even if wording is not identical) could be helpful for future-proofing?

On Fri, Aug 1, 2014 at 1:26 PM, Melissa notifications@github.com wrote:

Re: "repeatable"... im wondering if it makes sense to store follow up repeated questions separately

  • will the questionnaire benefit from slightly different wording for those questions so that users dont think the repeat is a bug.
  • the exported answers would also benefit in terms of usability for the research team if they had slightly different wording. Lots of mistakes can happen there so its sometimes worth it to put effort into usability of the data itself (humans have to instruct a computer what to do with the data after all!)
    • a answer's 1:1 relationship with a followup question instead of a 1:m relationship with the original question differentiated through a timestamp also helps make the data more human-friendly and consumable not only for the research team, but because it is going to be open data.
  • it involves potentially simpler implementation logic and I wouldnt have to change the validation that currently happens to prevent a user from submitting an answer twice ... maybe these questions are tagged in a way that they check the timestamp on the original question's answer before being added to the question queue...

Just thoughts, it would be easier to say when actually coding it.

— Reply to this email directly or view it on GitHub https://github.com/publiclab/wherewebreathe/issues/19#issuecomment-50911675 .

mmnoo commented 10 years ago

Agree.

jywarren commented 10 years ago

This is basically done now, closing!