CODEWITHSUNDEEP
mysqlsqlsqlite
yaylitzis
yaylitzis

Reputation: 5544

Table with many columns or many small tables?

I created a table where it has 30 columns. 

CREATE TABLE "SETTINGS" (
  "column1" INTEGER PRIMARY KEY,
  ...
  ...
  "column30"
)

However, I can group them and create different table where they can have foreign keys to the primary table. Which is the best way to follow? Or the number of the columns is small so it's the same which way I will follow?

Upvotes: 0

Views: 656

Answers (2)

symcbean
symcbean

Reputation: 48367

If they really are named column1, column2....column30 then that's a fairly good indicator that your data is not normalized.

Following the rules of normalization should always be your starting point. There are sometimes reasons for breaking the rules - but that comes after you know what your data should look like.

Regarding breaking the rules.....there are 2 potential scenarios which may apply here (after you've worked out the correct structure and done an appropriate level of testing with an appropriate volume of data):

  • Joins are expensive. holding parent/child relations in a single table can improve query performance where you are routinely selecting only both parent and child and retrieving individual rows

  • unless you are using fixed width MyISAM tables, updating records can result in them changing size, and hence they have to relocated in the table data file. This is expensive and can have a knock on effect on retrieval.

Upvotes: 2

Davide Lorenzo MARINO
Davide Lorenzo MARINO

Reputation: 26926

It depends on the data and the query you often do.

Best for one big table

  • If you need to extract all the columns always
  • If you need to update many fields at the same time
  • If all the fields or quite all have not null values

Best for many little tables

  • If the data are "sparse" it means not many columns have values you can imagine to split them in different tables and create a record in a child table only if not null values exists
  • If you extract only few related fields at one time
  • If you update only related fields at one time
  • Better names for each column (for example instead of domicile_address and residence_address you can have two columns with named address in two tables residences and domiciles)

The problem is that generally you can use both solutions depending from the situation. A usage analysis must be done to choose the right one.

Upvotes: 5

Related Questions