I need to create a table where each user (approx 60 atm) would have a defined task for each day. Right now the database have one column for each user with the task name in it (which is bad in my opinion as each new user would need to change the scheme of the table) and a "date" column.

A solution would be to have a "user" column and add a "task" column but that would mean there would be 60 (number of current users) rows per day.

I don't really know what's the best situation in this case.

CodePudding user response：

Should I use more columns or more rows?

They're two completely different things, so this comparison doesn't make much sense...

Right now the database have one column for each user

Bad idea. Full stop. A user is a record of data, not a structural element of the database itself. For example, a table of users might contain columns like Username, Email, RegistrationDate, etc. It would not be a single row of data in which you add a column for each new user.

This would be a nightmare to maintain, would render things like Foreign Keys useless (and, honestly, render the entire concept of a relational database useless), would reach resource limits very quickly, etc.

Each record of information is a row, not a column (or table). In this case, each row in your table is a "User Task". It defines (or has a Feorign Key to) a User and defines (or has a Foreign Key to) a Task.

but that would mean there would be 60 (number of current users) rows per day

If the number of records in the table starts to become a problem, you can start looking into things like sharding and partitioning, archiving old data, etc. You've got time though, because "dozens of records per day" is sustainable for thousands of years. (And by then I imagine the hardware will be at least twice as good as it is today.)

CodePudding user response：

Right now the database have one column for each user with the task name in it (which is bad in my opinion as each new user would need > to change the scheme of the table)

You're right, this is very bad. Using one column for user, one for the task and one for the date, will be much better.

60 rows per day is not much. This means 21.900 rows per years and 219.000 rows in ten years. Mysql is able to handle millions of rows in a table If you have two indexes, one for user and one for the date, searching for data will be fast enough.

CodePudding user response：

Knowing nothing else about your database or schema, why not create a dimension table to store your users and fact table to track your task details?

That way you can more easily add new users and the tasks table would continue to grow as new facts are added. It would also be very easy to denormalize this model for query and/or reporting purposes.