Significant slowdown due to 40 boolean columns?

Question

I am trying to add columns of a musical style to the event table, and from what I have compiled, this can be done by adding a column for each musical style or by performing a multi-valued table relation that I don't want, because I want each event to be returned only once in the table. Do you think that having so many boolean columns in a row that I would encounter a significant slowdown in the database? (Data will be read only by users). Thanks:)

Answer 1

Columns will not slow down the database as such, but keep in mind that adding a boolean column for each style of music is very bad. Over time, the possible musical styles in your application are likely to change: perhaps new ones should be added, redundant or useless should be deleted, whatever. With the proposed design, you will have to change the database structure to add new columns to the table. This is usually painful, error prone, and you will also have to go through all your inquiries to make sure they don't break due to the new structure.

You must design your database schema so that it is flexible enough to allow dispersion over time in the contents of your application. For example, you may have a table with one row for each musical style, defining its identifier and its name, description, etc. Then a relationship table that contains the relationship between the entity (event, if I understand your question correctly) and the musical style from the main table. You ensure consistency by putting foreign keys in place to ensure that the data is always clean (for example, you cannot reference a music style that is not in the main table). Thus, you can change the styles of music without affecting anything in the structure of the database.

Reading bits in database normalization will help you a lot; You do not need to fully work with a fully normalized database, but an understanding of the principles that will help you develop efficient and clean database structures.

Answer 2

Probable answer no

The presence of several Boolean columns in a row should not significantly slow down the database; if your indexes are configured accordingly.

EDIT: It might be best to assign a data table and JOIN to it to get that data ... but you said you didn’t want to do this.

I assume that you want to do something like an event row with a bunch of columns, such as "isCountry", "isMetal", "isPunk", and you will request all events marked as

 isPunk = 1 OR isMetal = 1 

or something like that.

The weakness of this design is that to add / remove musical styles you need to change the database schema.
An alternative is TBLMusicalStyles with ID and a Name , then a TBLEventStyles , which will only contain EventID and StyleID

Then you can join them and just do a search in the stylesheet ... and adding and removing styles will be relatively simple.

Answer 3

The performance of queries not related to music styles will not be affected.

If your columns are correctly indexed, queries that are related to finding rows that match the musical styles provided by the client should actually be faster.

However, all other queries related to musical styles will be much slower and more difficult to write. For example, “getting all the lines that have at least one style with the current line” would be much more difficult to request a record and execute.