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.

Improve this question
2
  • could you provide mock data or example data of what you are working with?
    – SQLserving
    Dec 28, 2017 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, 2017 at 17:49

2 Answers 2

Reset to default
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.

Improve this answer
1
  • 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, 2017 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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