Andrew Wilson Andrew Wilson - 6 months ago 37
MySQL Question

MySQL PHP Dynamic Data Select/Write

This is more of a design question, on the best way to do this.

I am trying to build an "inventorying" application into my project. I need to allow people to add an unlimited amount of sub-data.

Right now I'm unable to determine the best way to be able to store data as searchable (so I think blob is out). I dynamically store what they call

such as
Box Row Section
in a BLOB because there will only be one schema per user.

I'm storing the data on the location that needs to be searchable so if I have to look for
I can find it easily. I currently can search the column storing it as a plain text array with LIKE or REGEX, is that the best way to do this?


User 1 - Box 1
User 2 - Box 1 Row 3
User 3 - Box 1 Row 3 Section 4
User 4 - Box 1 Row 3 Section 4 Cell 7
User 5 - Warehouse 42 Isle 4 Shelf 4 Position 13

This is what I have so far:

- inventory (TABLE)
id INT(10)
itemid MEDIUMINT(7)
userid INT(10)
inventorylocation INT(10)
amount INT(10)
- inventorylabels (TABLE)
id INT(10)
userid INT(10)
schema BLOB(65535)
- inventorylocation (TABLE)
id INT(10)
location MEDIUMTEXT(16777215)

MySQL Fiddle


I would setup the table like this:

| id | user_id | param | sequence |

Put the order of the parameters in sequence (so just count 1,2,3,etc.) Then do a recursive query for each param.