2

I need help on designing a proper structure for a database table with dynamic fields. I am using MySql with PHP and the system I'm currently working on (inventory system) requires a table that accepts user-generated data categories. I'm really not good with this so any help will be greatly appreciated.

This is what I have right now (I know it's in really bad design please help)

Inventories can have different types (e.g Food, Clothes, etc) and details are dynamic based on what will be supplied by user (e.g Size, Brand, etc).

I have inventories_tbl:

+-------------------+
| inventories_tbl   |
+-------------------+
| id                |
| item_name         |
| inventory_type_id |
| description       |
| cost              |
| price             |
| quantity          |
| created_at        |
| updated_at        |
+-------------------+

then I have the inventory_types_tbl:

+--------------------+
|inventory_types_tbl |
+--------------------+
| id                 |
| name               | (e.g Food, Beverages, Clothing,etc)
| display_name       |
| created_at         |
| updated_at         |
+--------------------+

here comes the problematic part, I need to store dynamic inventory details according to what type of inventory type the item is. (e.g Food = 'expiration date', etc then if in Beverages = 'net volume', 'alcohol content', etc)

So I created a inventory_attributes_tbl:

    +-------------------+
 | inventory_attributes_tbl |
    +-------------------+
    | id                |
    | name              | (e.g size, weight, model number, serial number etc)
    | inventory_type_id |
    | field_name        |
    | created_at        |
    | updated_at        |
    +-------------------+

Then I have the inventory_attributes_values_tbl where I store the value of the attributes of a given inventory:

    +-------------------------+
  inventory_attributes_values_tbl
    +-------------------------+
    | id                      |
    | inventory_attributes_id |
    | inventory_id            |
    | value                   |
    | created_at              |
    | updated_at              |
    +-------------------------+

This setup makes the item delete/update so complicated and I also need to create a reports later on about based on the dynamic details of an item like graphs and this setup makes the query so slow and actually made the server to hang on querying just 10k+ of results. Any opinions are appreciated. Thanks in advance.

  • 1
    If you need to store the objects with a list of properties which may differ in count and type from one object to another one, look at EAV. Or think about serialization to JSON data type. – Akina Jul 20 '18 at 19:50
  • I agree with @Akina, Have you decided to work with MySQL? or "you have to". Firebase or MongoDB (EAV) are both accurate solutions for your case. blog.chapagain.com.np/… – AMG Jul 20 '18 at 20:07
  • Thanks for reply!, I am using mysql because of client requirements but this database structure makes delete/update so complicated because the inventory_attributes and inventory_attributes_values are dynamic and too slow because of deep joins. I am using JSON as php responses to my Vue.js components and it the fetching of data is really slow so I think it is because of my bad database structure design? – Jan Roenz Gaynilo Jul 20 '18 at 20:32
  • Read about MySQL's "Document Store". And see my aging blog on EAV . – Rick James Jul 20 '18 at 22:13
  • I guess Document Store is the way to go, I feel bad I really never thought this method. It is way better than EAV in terms of performance. Thank you so much sir @RickJames. And thank you guys for your replies! – Jan Roenz Gaynilo Jul 23 '18 at 14:48
1

Its possible to use type JSON

https://dev.mysql.com/doc/refman/8.0/en/json.html

this negates the need for a table of custom fields, however if you need to do join/look ups on this data, it will be difficult.

| improve this answer | |
  • It’s possible to index a field inside the Json. Not sure about relations/joins though. – malhal Jan 8 at 0:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.