title: Best database design to have efficient analysis on it with some millions records
body: <p>I have a basic question about database designing.</p>
  
  <p>I have a lot of files which I have to read and insert them in database. Each file has some thousand lines and each line has about 30 fields (by these types: <code>small int</code>, <code>int</code>, <code>big int</code>, <code>varchar</code>, <code>json</code>). Of course I use multi threads along with bulk inserting in order to increase insert speed (finally I have 30-40 millions records).</p>
  
  <p>After inserting I want to have some sophisticated analysis and the performance is important to me. </p>
  
  <p>Now I get each line fields and I'm ready to insert so I have 3 approaches:</p>
  
  <p><strong>1- One big table:</strong></p>
  
  <p>In this case I can create a big table with 30 columns and stores all of the files fields in that. So there is a table with huge size which I want to have a lot of analysis on it.</p>
  
  <p><strong>2- A fairly large table (A) and some little tables (B)s</strong></p>
  
  <p>In this case I can create some little tables which consist of the columns that have fairly identical records if we separate them from the other columns. So these little tables just has some hundred or thousand records instead of 30 millions records. So in fairly large table (A), I emit the columns which I put them in another table and I use a foreign key instead of them. Finally I has a table (A) with 20 columns and 30 millions records and some tables (B) with 2-3 columns and 100-50000 records for each of them. So in order to analysis the table A, I have to use some joins ,for example in select and ...</p>
  
  <p><strong>3- just a fairly large table</strong></p>
  
  <p>In this case I can create a fairly large table like table A in above case (with 20 columns) and instead of using foreign keys, I use a mapping between source columns and destination columns (this is something like foreign keys but has a little difference). For example I have 3 columns c1,c2,c3 that in case 2, I put them in another table B and use foreign key to access them, but now I assign a specific number to each distinctive records consist of c1,c2,c3 at inserting time and store the relation between the record and its assigned value in the program codes. So this table is completely like the table A in case number 2 but there is no need to use join in <code>select</code> or ... </p>
  
  <p>While the inserting time is important, the analysis time that I will have is more important to me, so I want to know your opinion about which of these case is better and also I will glad to see the other solutions.</p>