CODEWITHSUNDEEP
sqlpostgresql
TheBrownCoder
TheBrownCoder

Reputation: 1226

Structuring Month-Based Data in SQL

I'm curious about what the best way to structure data in a SQL database where I need to keep track of certain fields and how they differ month to month.

For example, if I had a users table in which I was trying to store 3 different values: name, email, and how many times they've logged in each month. Would it be best practice to create a new column for each month and store the number of times they logged in that month under that column? Or would it be better to create a new row/table for each month?

My instinct says creating new columns is the best way to reduce redundancy, however I can see it getting a little unwieldy when the number of columns in the table changes over time. (I was also thinking that if I were to do it by column, it would warrant having a total_column that keeps track of all months at a time).

Thanks!

Upvotes: 1

Views: 1162

Answers (3)

Jagrut Sharma
Jagrut Sharma

Reputation: 4754

Either approach is valid, depending on query patterns and join requirements.

One row for each month

For a user, the row containing login count for the month will be inserted when data is available for the month. There will be 1 row per month per user. This design will make it easier to do joins by month column. However, multiple rows will need to be accessed to get data for a user for the year.

-- column list
name
email
month
login_count

-- example entries
'user1', '[email protected]','jan',100
'user2', '[email protected]','jan',65
'user1', '[email protected]','feb',90
'user2', '[email protected]','feb',75

One row for all months

You do not need to dynamically add columns, since number of months is known in advance. The table can be initially created to accommodate all months. By default, all month_login_count columns would be initialized to 0. Then, the row would be updated as the login count for the month is populated. There will be 1 row per user. This design is not the best for doing joins by month. However, only one row will need to be accessed to get data for a user for the year.

-- column list
name
email
jan_login_count
feb_login_count
mar_login_count
apr_login_count
may_login_count
jun_login_count
jul_login_count
aug_login_count
sep_login_count
oct_login_count
nov_login_count
dec_login_count

-- example entries
'user1','[email protected]',100,90,0,0,0,0,0,0,0,0,0,0
'user2','[email protected]',65,75,0,0,0,0,0,0,0,0,0,0

Upvotes: -1

DxTx
DxTx

Reputation: 3377

I'm not an expert but in my opinion, it is best to store data in a separate table (in your case). That way you can manipulate the data easily and you don't have to modify the table design in the future.

PK: UserID & Date or New Column (Ex: RowNo with auto increment)

+--------+------------+-----------+
| UserID |    Date    | NoOfTimes |
+--------+------------+-----------+
|     01 | 2018.01.01 |         1 |
|     01 | 2018.01.02 |         3 |
|     01 | 2018.01.03 |         5 |
|     .. |            |           |
|     02 | 2018.01.01 |         2 |
|     02 | 2018.01.02 |         6 |
+--------+------------+-----------+

Or

PK: UserID, Year & Month or New Column (Ex: RowNo with auto increment)

+--------+------+-------+-----------+
| UserID | Year | Month | NoOfTimes |
+--------+------+-------+-----------+
|     01 | 2018 | Jan   |        10 |
|     01 | 2018 | feb   |        13 |
+--------+------+-------+-----------+

Before you create the table, please take a look at the database normalization. Especially 1st (1NF), 2nd (2NF) and 3rd (3NF) normalization forms.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

In my opinion, the best approach is to store each login for each user.

Use a query to summarize the data the way you need it when you query it.

You should only be thinking about other structures if summarizing the detail doesn't meet performance requirements -- which for a monthly report don't seem so onerous.

Whatever you do, storing counts in separate columns is not the right thing to do. Every month, you would need to add another column to the table.

Upvotes: 1

Related Questions