CODEWITHSUNDEEP
phpmysqlsqldatabase-designrelational-database
Dane Iracleous
Dane Iracleous

Reputation: 1759

SQL DB design for multiple-answer enum

What is an efficient design for having a field with multiple possible values out of a certain set?

For example,

favorite_animals: dog, cat, bird

possible_answers: snake, lizard, dog, hamster, cat, bird

I want to set it up so that queries are easy:

SELECT * FROM table WHERE favorite_animal='dog' OR favorite_animal='cat'
SELECT * FROM table WHERE favorite_animal='dog' AND favorite_animal='cat'

So basically, the field can have a value of 0 or more answers.

Upvotes: 2

Views: 502

Answers (2)

MrCode
MrCode

Reputation: 64526

For multiple values, the most efficient way is to use a second table to store the possible values, and a third table to store the association.

customers (id, name, gender ...)
animals (id, name) <-- one row for each animal
customer_animal_assoc (customer_id, animal_id)

The association table would store multiple rows if a customer has multiple favorite animals. This is an efficient and normalized structure that will scale if needed.

A query could look like:

SELECT
    c.name AS customer_name,
    a.name AS animal_name
FROM
    customer_animal_assoc caa
LEFT JOIN
    customers c ON c.id = caa.customer_id
LEFT JOIN
    animals a ON a.id = caa.animal_id
WHERE
    a.name = 'cat' AND a.name = 'dog'

Upvotes: 2

swapnesh
swapnesh

Reputation: 26722

You can use IN clause to check if it is in the given set or not.

SELECT * FROM table 
WHERE favorite_animal IN ('dog', 'cat', 'bird');

Upvotes: 1

Related Questions