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 :)

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.

The likely answer is no

Having multiple boolean columns in a row should not significantly slow down DB performance; assuming your indices are set up appropriately.

EDIT: That being said it may be optimal to assign a details table and JOIN to it to get this data... but you said you didn't want to do that.

I assume you want to do something like have an event row with a bunch of columns like "isCountry", "isMetal", "isPunk" and you'll query 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 your DB schema.
An Alternative is a TBLMusicalStyles with an ID and a Name, then a TBLEventStyles which will just contains EventID and StyleID

Then you could join them and just search on the styles table... and adding and removing styles would be relatively simple.

The performance of requests that do not involve musical styles will not be affected.

If your columns are properly indexed, then requests that involve finding lines that match client-provided musical styles should actually be faster.

However, all other requests that involve musical styles will be significantly slower, and also harder to write. For instance, "get all lines that share at least one style with the current line" would be a much harder request to write and execute.