Is it normal and good practice to enlarge the amount of columns when adding new features

Question

There is a table named is A with 40+ columns, among which around 10 are columns of hierarchical levels, in a data analysis platform maintenance and development ( legacy ) project. Due to development of a new feature, the hierarchical structure would need ten more extra levels, e.g. level11, level12, ..., project manager decided to add 20 new columns to that existing table A.

The hierarchial level data is sth like a revenue description by country and department: For instance,

-- value of level1: USA 100 | JAPAN 200 | UK 300

---- value of level2: USA SOFTWARE 50 | USA HARDWARE 50 | OTHERS JAPAN 50 | ...

------ value of level3: SOFTWARE A 10 | SOFTWARE B 10 ... | HARDWARE A 100 | HARDWARE B 200 ...

Each level's value is the breakdown of its parent level' value and there is no redundancy. So it is normalized, isn't it?

As a developer, I intuitively feel uncomfortable to add 20 more new columns to an existing table with already 40+ columns. But when considering the current table with 40+ columns works pretty fine, I am not able to argue with a sufficient reason against the idea of adding 20 more new columns to the existing table A.

Question: is adding that many columns to an existing table with already 40+ columns a very normal practice? What is the pros and cons in this approach?

Answer 1

From what I could understand, you have been following the pattern that your project manager has been imposing for a while, there may be valuable reasons for that decision to be made, have you tried asking him why their design for DB administration works like that? Honestly I don't see the point on organizing tables this way, but again, your DBA coworkers might answer that to you (and I hope you would be so kind as to share that answer with us, for mutual knowledge, after all that's what this community is all about and you don't need to if it involves confidential data).

While working on the database, I would suggest creating different tables, each with its own purpose and being related by foreign keys, but if you need to insert more columns (as such relationships may be very difficult to design and mantain in some specific situations), I don't see any real problem expanding the tables besides readability, the problem starts when updates are made to the properties of existing columns, same with any programming language, a good project should be expansible, but its properties shouldn't need to change by such expansions.

Answer 2

The number of columns is okay. Sometimes you just have a lot of columns. But what's concerning is that you have a lot of the same columns. You don't have actually different bits of data like first name, last name, street number, street name, postal code, date of birth, date hired, ...... - what you have is more like first name 1, first name 2, first name 3, first name 4, first name 5, first name 6, first name 7, ......

In this case (a many-to-one relationship of first names to rows) you should create a FirstName table whose rows each hold one first name, and the ID of the row they are associated with.

If the overhead of a separate table is unacceptable and you don't need to use the first names in queries and you don't expect to have a large number of first names in a row then you can instead have a FirstNames column which stores all the names in some format, such as JSON. Then you are treating the whole collection of names as one value, so it can be expensive if you have a very large number, and the database engine can't efficiently index the individual values, but it can be okay if you don't need those things.

Option 0 (bad because you have a maximum number of first names):

+------------------------------------------------------------------------------+
| TeamID | GoatsTeleported | FirstName1 | FirstName2 | FirstName3 | FirstName4 |
+------------------------------------------------------------------------------+
|      0 |              47 |      Alice |        Bob |      Carol |       Dave |
|      1 |            6542 |        Eve |      Frank |      Grace |      Heidi |
+------------------------------------------------------------------------------+

Option 1 (most flexible, adds another table):

+--------------------------+
| TeamID | GoatsTeleported |
+--------------------------+
|      0 |              47 |
|      1 |            6542 |
+--------------------------+

+--------------------+
| TeamID | FirstName |
+--------------------+
|      0 |     Alice |
|      0 |       Bob |
|      0 |     Carol |
|      0 |      Dave |
|      1 |       Eve |
|      1 |     Frank |
|      1 |     Grace |
|      1 |     Heidi |
+--------------------+

Option 2: (no extra table, but you can't query according to first names)

+------------------------------------------------------------+
| TeamID | GoatsTeleported |                      FirstNames |
+------------------------------------------------------------+
|      0 |              47 |  ["Alice","Bob","Carol","Dave"] |
|      1 |            6542 | ["Eve","Frank","Grace","Heidi"] |
+------------------------------------------------------------+