I have got two tables
plant_ID | name. 1 | tree 2 | shrubbery 20 | notashrubbery
area_ID | name | plants 1 | forrest | *needhelphere*
now I would like the region to keep a variety of plants, inside a specific order plus some plants might appear numerous occasions: e.g 2,20,1,2,2,20,1
What is the best method to store this variety of plants?
Bearing in mind I have to allow it to be to ensure that basically try a search to locate areas with plant 2, i do not get areas that are e.g 1,20,232,12,20 (pad with leading 0s?) What will be the query for your?
whether it helps, let us assume I've got a database of a maximum of 99999999 different plants. Company, this does not have anything related to plants....
Bonus Question Are you looking to step from MySQL? It is possible to better DB to handle this?
If you are likely to be searching both by forest by plant, seems like you'd take advantage of a complete-on many-to-many relationship. Ditch your
plants column, and make up a totally new
areas_plants table (or anything you want to refer to it as) to relate the 2 tables.
If area 1 has plants 1 and a pair of, and area 2 has plants 2 and three, your
areas_plants table would seem like this:
area_id | plant_id | sort_idx ----------------------------- 1 | 1 | 0 1 | 2 | 1 2 | 2 | 0 2 | 3 | 1
After that you can lookup associations from each side, and employ simple JOINs to find the relevant data from either table. You don't need to muck about in LIKE conditions to determine whether it's within the list, blah, bleh, yuck. I have had the experience for any legacy database. A real drag. Use SQL to the finest potential.
What about this:
plant_ID | name 1 | tree 2 | shrubbery 20 | notashrubbery
area_ID | name 1 | forest
area_ID | plant_ID | sequence 1 | 1 | 0 1 | 2 | 1 1 | 20 | 2
This is the standard stabilized method of doing it (having a mapping table).
To locate every area having a shrubbery (plant 2), do that:
SELECT * FROM areas INNER JOIN area_plant_map ON areas.area_ID = area_plant_map.area_ID WHERE plant_ID = 2
You realize this violates normal form?
Typically, you might come with an areaplants table: area_ID, plant_ID having a unique constraint around the two and foreign secrets towards the other two tables. This "link" table is exactly what provides you with many-many or many-to-one associations.
Queries about this are usually extremely powerful, they utilize indexes and don't require parsing strings.
Your relation characteristics ought to be atomic, not comprised of multiple values like lists. It's way too hard to look them. You'll need a new relation that maps the plants towards the area_ID and also the area_ID/plant combination may be the primary key.
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;
Simply to answer your bonus question:
Bonus Question Is it time to step away from MySQL? Is there a better DB to manage this?
It has nothing related to MySQL. It was just an problem with bad database design. You need to use intersection tables and several-to-many relationship for cases such as this in each and every RDBMS (MySQL, Oracle, MSSQL, PostgreSQL etc).