CODEWITHSUNDEEP
mysql
Jee Seok Yoon
Jee Seok Yoon

Reputation: 4806

Multiple tables for similar data in MySQL

I am writing a server using Netty and MySQL(with JDBC connector/J).

Please note that I am very new to server programming.

Say I have an application that users input about 20 information about themselves. And I need to make some methods where I need only specific data from those information. 

Instead of using "select dataOne, dataTwo from tableOne where userNum=1~1000"
create a new table called tableTwo containing only dataOne and dataTwo.
Then use "select * from tableTwo where userNum=1~1000"

Is this a good practice when I make tables for every method I need? If not, what can be a better practice?

Upvotes: 1

Views: 59

Answers (1)

Menelaos
Menelaos

Reputation: 25727

You should not be replicating data.

SQL is made in such a way that you specify the exact columns you want after the SELECT statement.

There is no overhead to selecting specific columns, and this is the way SQL is designed for.

There is overhead to replicating your data, and storing in 2 different tables.

Consequences of using such a design:

  • In a world where we used only select * we would need a different table for each combination of columns we want in results.
  • Consequently, we would be storing the same data repeatedly. If you needed 10 different column combinations, this would be 10X your data.
  • Finally, data manipulation statements (update, insert) would need to update the same data in multiple tables also multiplying the time needed to perform basic operations.
  • It would cause databases to not be scalable.

Upvotes: 3

Related Questions