One table with a changing variable or different tables? I have a problem where all the data except one column, which is known to have 4 different values, will be a variable.

Other than that column everything else will be of same type(only column names are same but data will be different)

For smaller applications I hope it is OK to just add the flag and make it one table.

I will be working with data containing around 100,000 rows and increasing everyday at the below ratios. If split they will be like

Type 1 - 60,000
Type 2 - 20,000
Type 3 - 10,000
Type 4 - 10,000 


I want a better way to implement especially on MySQL but information about Oracle is much appreciated as I want to gain knowledge in that too.

What happens if I add this to a single table and make the type as index so the burden for where conditions will be low?

How comparable is that to having 4 different tables?

Personally, I don't like the idea of 4 tables but I am looking for an answer in performance perspective.
You have a common problem, which is how to implement table inheritance into a relational database. There is not perfect solution, as it will be fully dependent on how you are going to use your database afterwards and what you want to optimize for.

Typical way to implement your solution is one of 3 ways: create 4 separate tables, create a single table, or create 5 tables, one for the "parent" relationship and 4 for the "children". You can find more discussion on the advantages or disadvantages of each solution on:


SQL Antipatterns Book
SQL Alchemy
Many other Q&amp;A on StackExchange such as: How to model inheritance of two tables mysql


Because the question is too general, only a general answer can be answered -optimize for the most common kind of operations you are going to be performed later; or for the restrictions you may have (simplicity of the code, minimize data size on disk, minimize CPU usage, etc.), some of which will depend on the specific dataset you have and the database storage engine you are using.