I received a bit of advice approximately one year ago with regards to designing database tables. It boiled down to “treat different things differently by putting them in separate tables that have been designed for that specific thing”.
I think that is great advice generally. One of the problems I saw at a past position was that they had one table that was storing three fairly different things. The end result was that the table was difficult to work with, and the code base was more complex than it needed to be in order to deal with the various different edge cases in that table.
In a recent project, I architected a solution that dealt with a number of different tables that all inherited from the ServiceNow task table. My proposal was to have a different custom table for each of the three tables that were children of task.
My boss countered by suggesting that we have just one custom table that dealt with all three of the stock ServiceNow tables, and add another column to it that had the name of the table that particular entry related to. He indicated that building the back end that way would be more scalable if additional tables needed to be covered by my project at a later date, and he was exactly right.
So, my addendum to the rule that I’ve been following for the last year or so is that you want to treat different things differently, and give them each their own table, but things that appear to be different at first glance might not actually be as different as you think. If you’ve got the same fields/columns across different tables, and they are all being populated, then you could probably replace the tables with a single table and use some kind of ENUM to categorize the records appropriately.
All of which will tend to make your solution more scalable.