I am currently get a job and faced to a Table with around 900 columns in their database.

I am not sure what should I do instead of re-design the database, Please advice me for this situation that I have.

Queries for this Table is really frustrating when I need some of the data in this table and joining to other tables.

(This table designed before me,)



The best way to deal with any software problem is to understand, what do you need to achieve and find an achievable, reasonable solution for it. It is not apparent from your question, what your goal is.

I do not see how joining with other tables for a table with 1k columns is more frustrating that for a table with 100 columns or 10 columns. It is still an x JOIN y ON x.some_id=y.pk_id. The only possible frustration is if people don't use synthetic keys and rely on natural primary keys of multiple columns, then JOIN becomes a bit long and a bit slow. That said, there are tables for data warehousing, that have to be done this way for performance purposes. Otherwise, introduce synthetic auto-incremented or world-unique keys and voilà. BTW, world-unique keys (aka UUIDs) are only needed if distribution and out of band merging is required. For warehousing, learn about snowflakes and star schemas and maybe re-organize your data or simply create a derivative replication job into a separate database.

Does a table with 900 columns scream of a bad design? Most certainly. Can you improve on this design? Probably, yes. Is it your immediate goal? Not sure, probably not.

In a reasonably mature database that is covering a complex business domain, newcomer frustration is unavoidable. Good architects are able to catch the moment where maintaining a complex database is too much debt, even it is just a debt of training new employees, and start splitting it up into more manageable parts with separate services on top of it. Maybe even microservices. But, there is no free lunch in it. You would have to overcome a number of hurdles before you reach the same level of speed and functionality after splitting. It might even cause your company to suffer financially and loose a market share. All of this simply for a potential to grow and scale better. Sometimes risks outweigh benefits and you end up with a schema of 1k tables with some of them having 1k columns. Welcome to the real world. No one pays for SELECT * FROM Customer and console.log("Hello, World!").

If you would like a better answer, ask a better question. Good question should always be along the lines "I have this situation. And I need to be in this situation. And this is what I researched and tried. And here is my question: [actual question, no shortcuts, not asking for advice, not 'what should I do', a real question needs to be here]". Good luck!

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