Geek asks and answers

SQL database: use one table with 3.5 million records or many tables with fewer records?

I am creating a soccer match that allows people to create leagues online.

Say I have 1000 leagues after a while. I store my data in SQL. Should I:

1) Create 1 single database with the “rosters” table and insert all the players of all leagues into it (which would be from 3.5 to 4 million entries per 1000 leagues).

2) Create 1 single database with 20 tables "rosters1, rosters2, etc." and divide the leagues between the 20 tables

3) Create 1 database for each league with a table of “registries” in each

4) Create 20 databases with one table of “lists” in each and divide the leagues between 20 databases.

What is the best option for performance and fast SQL queries?

+3
source share
2 answers

It sounds like you need to learn a few basic database fundamentals, first, so please spend some time making sure you understand Database Normalization and.

However, in principle, you probably need a table of "players" (player identifier, name, etc.), a list table (roster identifier, name, possibly owner_id) and player_roster_map (player_id, roster_id), which links the two. You will probably need a unique constraint on the composite value of player_id and roster_id, and you should have a foreign key constraint on both player_id and roster_id in this mapping table.

( , , , , , , , .)

, , , , , , , MySql; .

. , (, ), . , . ; , , , , .

+1

, 1 . , , - : , . , . , , MySQL . (Postgresql .)

. . , teams, PlayerTeam - ( BTW). , - - .

+1

More articles:


All Articles