CODEWITHSUNDEEP
sql
ChiragAgarwal
ChiragAgarwal

Reputation: 5

Which database structure is efficient? One table with 10,000 records or 1000 tables with 10 records?

We at college are making an application to generate PDF document from Excel sheet records using Java SE. I have though about two approaches to design the database. In one approach, there will be one table that will contain a lot of records (50K every year). In other approach, there will be a lot of tables created (1000 every year) at runtime and each table will contain max 50 records. Which approach is efficient comparatively considering better overall time performance?

Upvotes: 0

Views: 949

Answers (4)

Razvan
Razvan

Reputation: 3142

When building a relational database the basic rule would be to avoid redundancy.

Look over your data and try to separate things that tend to repeat. If you notice a column or a group of columns that repeat across multiple entries create a new table for them. This way you will achieve the best performance when querying.

Otherwise, if the values are unique across the entries just keep the minimum number of tables.

You should just look for some design rules for relational databases. You will find some examples as well.

Upvotes: 0

OldProgrammer
OldProgrammer

Reputation: 12169

50K records is not "a lot" of records. You don't specify what database you will be using, but most commercial-grade databases can handle many, many millions of records in a table. This is assuming you have proper indexes, etc. If you have to keep creating tables for you application, then there is something wrong with your design, and you need to re-think that.

Upvotes: 1

Jan Fabry
Jan Fabry

Reputation: 7351

50k records is not much for a database. If it's all the same type of data (same structure), it belongs in the same table. Only if size and speed becomes an issue you should consider splitting up the data over multiple tables (or more likely: different servers).

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

Multiple tables of identical structure almost never makes sense.

Databases are designed to have many records in few tables.

Upvotes: 8

Related Questions