I am super new to sql/databases. So I have lots of data. Sorted by date with multiple entries for each day for the last 22 years (~8000 days).

My solution was to create a new table for every date and write entries in it. eg.

Table - 2020-10-23:

Table - 2020-10-24:

Like this there would be around 8000 of these tables and each of them would have around 800 entries. So around 6 Million entries.

So the first thing I want ask is whether this is an viable solution because I need to create more tables for user data, authentication, etc in the same database.

And second thing whether SQLite is the right database for this.

CodePudding user response：

You do not need to create tables for each day. You need to look at what you need to store. Since you have not given any contextual information, I will provide you only general guidelines that you can append to your actual data. If things will be unclear, then you will need to explain what you want to store.

So, as a general rule, whatever is a single value for each entity is considered to be an attribute. Whatever is composite, should be considered a separate entity type. You will need to prevent inconsistencies and redundancy by ensuring that your database is in normal form. If your data is interrelated and logically describes a single entity for each record, then you will have a single table for now, having a date field and a bunch of other fields as well. If storing everything into a single table is not a feasible approach due to inconsistencies and redundancy, then you will need to break your table down into further tables.

Your pictures suggest that the columns are rows. It's probably a sign of planning errors.

8000 * k (where k on average is less than 1 000) records are not many in the case of modern RDBMS tools.

When you select records, you can filter/group by the day, achieving the behavior you could have achieved with the different tables you have.

Whether to use or not to use SQLite it is your decision. However, SQLite is a lightweight SQL tool, if you want to go professional, you might want to switch to something else, like MySQL, Oracle, SQL Server or PostgreSQL.