#1
  1. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,082
    Rep Power
    379

    Storing user data.. need effective design


    Hi

    My users will be using the system to declare things they have done which needs to be done everyday. So my system needs to record if they did those things or they didnr

    Furthermore if they havent done these things in the past my system will tell them so and remind them. So think of my system where every user has to take a pill everyday from thw day they are born and if they havent the system will remind them.

    I think if i store each row per day it will get too much. For each user the rows could go over 36531 assuming u r 31 but this will increase by 365 each year.

    I was then thinking to store a week per record something like 10001 (boolean so 0 means no 1 means yes) but code would get messy and it still wouldnt solve the problem that each year the user data would increase
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,727
    Rep Power
    4288
    premature optimization is the root of all evil

    "it will get too much" is a completely baseless fear

    boolean? i see you've already considered what kind of sql you'd need, e.g. to compare thursdays to sundays

    as an aside, i would laugh at an app that required me to input whether i took my pills today...

    sorry to be such a debbie downer this fine monday
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2016
    Posts
    19
    Rep Power
    0
    i know i havent given a proper example... but if you get over the fact that an app was built to let you input whether you put your daily pills 3 or 4 times a day.. and assuming you may have 1000s of people who could also go back and 'fill' in past results

    How would one go about creating the design (tables?)


    (disclaimer: i registered that username under my uni email or something and i dont have access to that anymore, and somehow my password wasnt working :s so i have had to create a new username as this question is quite important)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2015
    Location
    Canada
    Posts
    24
    Rep Power
    0
    This doesn't answer your question about the database design.
    But, have you had a look at an app like Todoist?
    It has daily / recurring tasks, worth a look it might help you refine your idea!
  8. #5
  9. Not An Expert
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2015
    Posts
    404
    Rep Power
    3
    Originally Posted by shez1983
    (disclaimer: i registered that username under my uni email or something and i dont have access to that anymore, and somehow my password wasnt working :s so i have had to create a new username as this question is quite important)
    Hello shez1983 (aka paulh1983?)

    I can help reset the password for your old account, if you wish. Send me an email at markr@imninjas.com asking about it (give your username) and we'll work out a way to prove you're the same person and get you back into your old account
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2016
    Posts
    19
    Rep Power
    0
    i know for an urgent question i havent kept up with it..

    there must be someone who has dealt with large amount of info and how to deal with it? maybe i should look at mongodb or something else?
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,727
    Rep Power
    4288
    Originally Posted by shez1983
    there must be someone who has dealt with large amount of info
    there are, indeed, many of us

    what is your actual question again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2016
    Posts
    19
    Rep Power
    0
    Originally Posted by r937
    there are, indeed, many of us

    what is your actual question again?
    i would like some pointers on how to store large data like mentioned previously. .so i have to calculate what a user does each day possibly 5 times, perhaps more.. so thats easily 6 rows per user per day, plus they can go back and do stuff they missed in the past..

    so in my app what would happen:
    1. user logs in.
    2. be presented with things they must do each day - (tasks)
    3. have the option of adding on extra tasks that they may wish to perform (daily or weekly).

    so interface would say:

    Tasks for today: checkboxes with label
    Tasks for this week:checkboxes with label
    previously missed tasks: checkboxes with label

    when they click on a checkbox a row is inserted into db.

    i am just worried that this will generate lots of data and i will have to refactor the db tables and i would rather do it now then later on..

    they will also get stats ie how many tasks are still left (from the ones they have missed in the past). .so as they complete previously missed tasks, this total will decrease
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,727
    Rep Power
    4288
    Originally Posted by shez1983
    i am just worried that this will generate lots of data
    how much is "lots" ?? gigabytes? pfft, that's not lots

    as long as your server has enough disk space and processing power, you'll be fine

    the secret is in designing the tables and indexes properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo