Whats the most efficient way to store an array of integers in a MySQL column?

Question

I've got two tables

A:

plant_ID | name.
1        | tree
2        | shrubbery
20       | notashrubbery

B:

area_ID | name    | plants
1       | forrest | *needhelphere*

now I want the area to store any number of plants, in a specific order and some plants might show up a number of times: e.g 2,20,1,2,2,20,1

Whats the most efficient way to store this array of plants?
Keeping in mind I need to make it so that if I perform a search to find areas with plant 2, i don't get areas which are e.g 1,20,232,12,20 (pad with leading 0s?) What would be the query for that?

if it helps, let's assume I have a database of no more than 99999999 different plants. And yes, this question doesn't have anything to do with plants....

Bonus Question Is it time to step away from MySQL? Is there a better DB to manage this?

Answer 1

If you're going to be searching both by forest and by plant, sounds like you would benefit from a full-on many-to-many relationship. Ditch your plants column, and create a whole new area_plants table (or whatever you want to call it) to relate the two tables.

If plant 1 is in areas 1 and 2, and plant 2 is in areas 2 and 3, your areas_plants table would look like this:

area_id | plant_id | sort_idx
-----------------------------
      1 |        1 |     0
      1 |        2 |     1
      2 |        2 |     0
      2 |        3 |     1

You can then look up relationships from either side, and use simple JOINs to get the relevant data from either table. No need to muck about in LIKE conditions to figure out if it's in the list, blah, bleh, yuck. I've been there for a legacy database. No fun. Use SQL to its greatest potential.

Answer 2

You know this violates normal form?

Typically, one would have an areaplants table: area_ID, plant_ID with a unique constraint on the two and foreign keys to the other two tables. This "link" table is what gives you many-many or many-to-one relationships.

Queries on this are generally very efficient, they utilize indexes and do not require parsing strings.

Answer 3

Your relation attributes should be atomic, not made up of multiple values like lists. It is too hard to search them. You need a new relation that maps the plants to the area_ID and the area_ID/plant combination is the primary key.

Answer 4

How about this:

table: plants

plant_ID | name
1        | tree
2        | shrubbery
20       | notashrubbery

table: areas

area_ID | name
1       | forest

table: area_plant_map

area_ID | plant_ID | sequence
1       | 1        | 0
1       | 2        | 1
1       | 20       | 2

That's the standard normalized way to do it (with a mapping table).

To find all areas with a shrubbery (plant 2), do this:

SELECT *
FROM areas
INNER JOIN area_plant_map ON areas.area_ID = area_plant_map.area_ID
WHERE plant_ID = 2

Answer 5

Use many-to-many relationship:

CREATE TABLE plant (
    plant_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=INNODB;

CREATE TABLE area (
    area_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=INNODB;

CREATE TABLE plant_area_xref (
    plant_id INT NOT NULL,
    area_id INT NOT NULL,
    sort_idx INT NOT NULL,
    FOREIGN KEY (plant_id) REFERENCES plant(plant_id) ON DELETE CASCADE,
    FOREIGN KEY (area_id) REFERENCES area(area_id) ON DELETE CASCADE,
    PRIMARY KEY  (plant_id, area_id, sort_idx)    
) ENGINE=INNODB;

EDIT:

Just to answer your bonus question:

Bonus Question Is it time to step away from MySQL? Is there a better DB to manage this?

This has nothing to do with MySQL. This was just an issue with bad database design. You should use intersection tables and many-to-many relationship for cases like this in every RDBMS (MySQL, Oracle, MSSQL, PostgreSQL etc).