CODEWITHSUNDEEP
mysqlperformanceboolean
Spider-Paddy
Spider-Paddy

Reputation: 413

Many Bool columns in database table

I recently took over a website where people can register to help tutor kids. Part of the user's details is which areas they could work, represented by postal codes. The problem is, my predecessor designed the site such that in the database there is a Boolean column for every postal code. As such, the user table has almost 270 columns and can be quite slow at times (plus it's a nightmare to administer).

Most users select only a few postal codes so there is surely a better way to do it. I was thinking about a varchar that could save the selected areas comma separated, e.g. 6043,8811,1234

Any advice from somebody who's had the same problem?

Upvotes: 0

Views: 52

Answers (2)

kr094
kr094

Reputation: 139

You should throw that entire idea out of your head and look into properly normalized data.

A possible solution to this problem would be a table for tutors, which has an id column to uniquely identify one tutor.

Then you would have a table for just Postal Codes (each with unique ids as well) and finally a tutor_availability table that creates one record of (t_id, pc_id) for each postal code a tutor wishes to offer their services, again with a unique id to avoid duplication risks in the case they can select the same location twice.

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

both your predecessor's and your solution are... strange.

You should simply have a relationship table between user and localities (assuming you have a locality table, with a postalCode field and a surrogate key (id)).

UserLocality(userId int, localityId int)

so a locality could have many user, and a user could have many localities.

Coma separated fields is a really bad idea, when query time comes.

Upvotes: 3

Related Questions