Redesigning a table that has a lot of columns (not always related)

Question

I'm new to database stuff so I apologize if I say anything ridiculous..

I work for a company that runs a website for a certain industry. We're not very large at the moment but our parent company wants to push our product out to more places. I'm worried about this because our database was never designed with ANY plan in mind (e.g, normalization doesn't exist, there are maybe 70 unused tables out of about 400- plenty of more unused columns on the tables we do use, etc.,) and we're running on a really shitty database program (pervasive SQL).

The company has at least recognized the need to move away from pervasive. We're planning to go to mariadb. To go along with this, there is a rare opportunity to potentially redesign some parts (or all of) the database but no one really cared to look into it- and that's how I ended up here.

SO, all that being said I just want to know if I'm on the right track here with my redesign so far.

Let's say we have a table that holds basic settings for companies on our system. Some of these settings are relevant to notifications (i.e what events will trigger e-mails for them), some are related to billing, some to how they create orders / what info they see on their orders, etc., This table has 334 columns and a LARGE amount of them only store a CHAR(1) which can be Y/N. (Side note: am I correct in assuming BIT will take up less space?)

What I've been doing is separating the parts (i.e, settings for orders go in their own company_order_settings table which has the company's id in it). The only thing is at this point I already have ~8 tables just for these different settings...is this normal? In general these things will (usually) be accessed separately so I'm not concerned about large joins. This large number of purely settings related tables will also happen when I refactor the user tables (which hold info on the users at said companies- in a similar number of columns).

If anyone wants more info let me know.

Answer 1

Not really an answer - but too big for a comment. Just a few ideas/discussion.

"our database was never designed with ANY plan in mind" - I hear yeh, sister!

Been there, done that. Once worked with a Btrieve (precursor to Pervasive) system that had 35,000 (not a typo - that's thirty five thousand) fields in one table. Agree with @timpone - hesitant about tables with > 30 fields. Tables should be like women - tall and slim, not short and fat :-).

It's a lot of work to redesign. Start with core functionality - what's the minimum amount of tables required? I would consider PostgreSQL if I were you, but like @Austin, that's just my opinion.

Read up on the different RDBMSs - check out their functionality. Is there anything that Btrieve has that they do/don't? As far as I can see, the main thing that Btrieve brought to the party was arrays - they are a breach of the relational model and should be avoided at all costs in an RDBMS.

You say "a LARGE amount of them only store a CHAR(1) which can be Y/N. (Side note: am I correct in assuming BIT will take up less space?)". With space being so cheap these days, this is not really an issue.

Answer 2

8 or less BIT columns will use up 1 byte space.
Char(1) will use up 1 byte too.
But! If you'd define 8 columns as BIT, it will still eat up 1 byte. If you define 8 columns as char(1), well ...

I think you are doing fine. Though I would try to figure out what and how the data is selected. Analyse the queries, and you'll know what columns are rarely or even never used.

Though it is hard work and takes time, I'd invest time to create a good old ERD. With that ERD, you'll have a visual representation of what you are dealing with. From there it is much easier to shift around, add or remove tables and columns. Never underestimate the power given with your plan/map.

I would invest some time to think about junk data. Might be a lot of settings, that are used once a year. I wouldn't want keep them where the other hot columns are. Why? Indexing.

Multiple tables for settings can be normal, yes. If for example they are seperated by usecases, then it makes perfect sense to me.

I'm working on the same problem in my company. So maybe a couple of hints from my experience.
Make sure the applications and reportings are considered in your structural changes.
Make sure users are sensitized, for they will have queries that won't work any more.
Make sure you always have a backup before you run your changes. Backups are your lifeline.

Answer 3

If I were you I would go with MSSQL Server, but that is just my opinion. As far as the database design goes I prefer to make a spreadsheet of my tables when I have time to look at my information and logically separate it. I recently had to turn a huge directory of XML files into a SQL database and had little to go from. I spent a lot of time studying the files and then making a structure based off of the information. I think you are on the right path so far. The more you can separate the information and prevent duplicate data and relate to other information the better.