1

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.

  • could you provide mock data or example data of what you are working with? – SQLserving Dec 28 '17 at 17:44
  • An Example would be a person table of school with 4 departments, Teacher, Student, Non Teaching Staff, Administration Department. Where name, email, phone number, address are common but only the department changes. – Amarnath Maddireddy Dec 28 '17 at 17:49
0

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:

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.

  • Thank you so much. I didn't know that word "inheritance" can be used here in DB. I now know what to search for. – Amarnath Maddireddy Dec 29 '17 at 9:46
0

Sure, you have a Persons table, where there are 4 types of people. Think of it that way. "Inheritance" seems to be a more complex subject than you need for your simple case.

You may get into trouble if you have complex SELECTs, but I doubt it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.