Search code examples
phpmysqldatabasedata-collection

Database Design/Structure -- Collecting Data over time

currently I am in the process of structuring a database for a site I am creating. However, I have come across a problem. I want to log the amount of times a user has logged in each day, and then be able to keep track of that info over large periods of time such as a 8 months, a year, 2 years, etc.

The only way I can think of right now, is to just have a column for each day of the year/automatically create a column each day. This idea however, just seems plain stupid to me. I'm sure there has to be a better way to do this, I just can't think of one.

Any suggestions?

Thanks, Rob

Solution

  • Create a separate table where you store user_id, and datetime the user logs in.

    Averytime the user logs in, you insert a new record on this table.

    Example:

    CREATE TABLE user_activity (
         userid varchar(50),
         log_in_datetime datetime
       );