Data Engineer at n/a·

We have a 138 row, 1700 column database likely to grow at least a row and a column every week. We are mostly concerned with how user-friendly the graphical management tools are. I understand MySQL has MySQL WorkBench, and Microsoft SQL Server has Microsoft SQL Server Management Studio. We have about 6 months to migrate our Excel database to one of these DBMS, and continue (hopefully manually) importing excel files from then on. Any tips appreciated!

READ LESS
3 upvotes·143.5K views
Replies (1)
Software Engineer ·

I'm not sure I can comfortably recommend either tool as i don't believe either is really suitable for the use case you describe. In fact, I would question the soundness of the use case in the first place. The whole idea behind RDBMs (like Microsoft SQL Server and MySQL) is to store data in a relational way. To have a table with 1700 columns sounds foreign to the RDBMs paradigm already but to have that number growing by one every week is a big design smell. As a rule of thumb, making database schema changes should be avoided if at all possible. Adding a column to a table is a schema change and you're talking about making a schema change on a weekly basis. Adding rows is fine. That's what RDBMs are good at.

Having said all this, SQL might well be perfectly suited for your needs but I would suggest you rethink the way you organise your data. There's a very good chance that you might be able to represent all the data you need by making use of a hand ful of normalised tables, as opposed to one enormous flat table such as you're describing.

READ MORE
2 upvotes·3 comments·2.4K views
Kelsey Doolittle
Kelsey Doolittle
·
February 17th 2021 at 8:31AM

Firstly, thanks so much for the response. I guess it is helpful to clarify that this is our CURRENT setup, and indeed we are hoping to divide into more tables to better access the data. I also worry about changing the database schema on a weekly basis. This is unfortunately just the nature of our data. Do you have recommendations for another type of database structure that would better fit this? We looked into NoSQL but couldn't find concrete evidence that this would be better suited to our needs.

·
Reply
Dewald Swanepoel
Dewald Swanepoel
·
February 18th 2021 at 4:10PM

Yeah I'm afraid I don't think I'm gonna be of much help to you. It depends of course on how you want to extract data but my gut feel says that a document type dbms (nosql) wouldn't be all that useful either. But without better knowledge of your data I can't really say. Nosql databases are ideal for data with a loosely defined structure but it sounds like yours is not loosely defined but ever changing.

·
Reply
Kelsey Doolittle
Kelsey Doolittle
·
February 19th 2021 at 8:13AM

Well it sounds like perhaps it doesn't matter what SQL RDBMS I use, just necessary to have a good schema and a schema change management strategy of sorts

·
Reply
Avatar of Kelsey Doolittle

Kelsey Doolittle

Data Engineer at n/a