31296

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

Answer1:

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.

Answer2:

The likely answer is <strong>no</strong>

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

<strong>EDIT:</strong> 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.

Answer3:

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.

Recommend

  • Plotting a 3D surface in R
  • AutoMapper doesn't map calculated field to scalar
  • Calculating grouped variance from a frequency table in R
  • Meaning of “trait” in MCMCglmm
  • Get working days in SQL Server
  • Calculate letter size in javascript
  • “Unable to evaluate the expression” in Visual Studio 2012 Debug Mode
  • Should i use nhibernate for a fairly small project?
  • Relationship between orders, customers and products?
  • How to normalize a database schema
  • How to translate SQL queries to cypher in the optimal way?
  • Defining variable by logical subseting on time interval in data.table
  • Validity Method for Reference Classes
  • ASP.NET, C# How to Pass a StringQuery to a custom SQL Command
  • WP7 difficulties binding data to listbox itemssource - won't refresh
  • Nested projects in multiproject visual studio templates
  • TSQL Rolling Average of Time Groupings
  • How to populate html table with info from list in django
  • Ruby 1.8.6 Array#uniq not removing duplicate hashes
  • Cypher - matching two different possible paths and return both
  • Prevent page break in text block with iText, XMLWorker
  • Retaining data after updating application
  • Examples of how to a STS in .Net 4.5 using WCF
  • Mixing WebForms and MVC: What should I do with the MasterPage?
  • Shouldn't else be indented in the below code
  • C++ Single function pointer for all template instances
  • Configure Spring's MappingJacksonHttpMessageConverter
  • MySQL performance when updating row with FK
  • JSR-330 support in Picocontainer : @Inject … @Named(\"xxx)
  • How to get links to open in the native browser in iOS Meteor apps?
  • How to autopopulate a field in SugarCRM form
  • AppleScript : find open tab in safari by name and open it
  • OOP Javascript - Is “get property” method necessary?
  • WPF - CanExecute dosn't fire when raising Commands from a UserControl
  • ActiveRecord query for a count of new users by day
  • How to use an array of arrays with array_map(…) in PHP?
  • Ajax Loaded meta Tags
  • Master page gives error
  • Redux, normalised entities and lodash merge
  • How do I rollback to a specific git commit