CODEWITHSUNDEEP
mysqlindexing
tfe
tfe

Reputation: 253

MySQL database design: multiple values in the same field

I need to create an index on 3 columns:

`gender` tinyint(1) NOT NULL default '0',
`age` tinyint(2) NOT NULL default '0',
`is_looking_for` varchar(30) NOT NULL default ''

The problem is third column which is VARCHAR and this index will not be used. I can't make it INT. Each user who creates a profile in this dating site answers question "I am looking for.." and she/he can select multiple options, for exmple,
looking for male
looking for female or couple (female+female)
looking for male or female, couple (male+female)

Now i store this information in a VARCHAR (is_looking_for) field in this way:

1,3,5

and MySQL query looks like this:

select from profiles where gender='$gender' and age between $minage and $maxage 
and is_looking_for LIKE '%$lookingfor%';

This query is very bad but I don't know how to resolve this problem. I don't want to create different columns for each gender option - male, female, couple(m+f), couple(f+f) etc. because then I need to create multiple indexes - a separate index for each gender:

KEY `gender` (`gender`,`age`,`female`)
KEY `gender_1` (`gender`,`age`,`male`)
KEY `gender_2` (`gender`,`age`,`couple_male_female`)

etc.

I had an idea to assign an unique number to each possible combination and then to store this number in a INT field but I gave up because it's too difficult.

Can help somebody? Thanks.

Upvotes: 0

Views: 293

Answers (2)

Marc B
Marc B

Reputation: 360872

Normalize your design. Create a separate table that lists all those "looking for" variables, each with their own unique ID.

1. looking for male
2. looking for female
3. looking for male+female
4. looking for ...

Then another table that links this new table to your dating table:

date_id    looking_for_id
69         2
69         4

which would signify that date #69 is looking for a female, or a ...

Upvotes: 2

Rene Pot
Rene Pot

Reputation: 24815

You should probably create an ENUM. The ENUM is meant for that.

Take a look at the docs: http://dev.mysql.com/doc/refman/5.0/en/enum.html

Upvotes: 0

Related Questions