Search code examples
mysqlnormalization

Considerable slowdown due to 40 boolean columns?


I'm trying to add musical style columns to an event table and from what I've gathered this can be either done by adding a column for each musical style or by doing a many-to-many table relation which I don't want because I want each event returned only once in a table. Do you think that having that many boolean columns in a row, that I would face considerable database slowdown? (Data will only be read by the users). Thank you :)


Solution

  • The columns will not slow down the database per se, but keep in mind that adding a boolean column for every music style is very poor design. Over time, the possible musical styles in your application are likely to change: maybe new ones must be added, redundant or useless ones must be removed, whatever. With your proposed design, you'd have to modify your database structure to add the new columns to the table. This is usually painful, error-prone, and you'll also have to go over all your queries to make sure they don't break because of the new structure.

    You should design your database schema so that it's flexible enough to allow for variance over time in the contents of your application. For example, you could have a master table with one row for every musical style, defining its ID and its name, description etc. Then, a relationships table that contains the relationship between an entity (event, if I understood your question correctly) and a music style from the master table. You enforce consistency by putting foreign keys in place, to ensure data is always clean (eg. you cannot reference a music style that is not in the master table). This way ,you can modify the music styles without touching anything in the database structure.

    Reading a bit on database normalization will help you a lot; you don't have to go all the way to have a fully normalized database, but understanding the principles behind will allow you to design efficient and clean database structures.