Need help designing a Database structure with dynamic fields, PHP + MySql

[valkyrie07]

I need help on designing a proper structure for a database table with dynamic fields. I'm using MySql with PHP and the system I'm currently working on (inventory system in Laravel) 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.

 

[JackFrost10]

kilangan mo po sir ng table with each type and attributes ng item at dun ilagay ang data.

example:

beverage_table - <--- item type
-unit_id
-item_id <--- foreign key para sa inventory table
-attribute_1 <--- attributes
-attribute_2 <--- attributes

about dynamic item types nmn, pwede ka pong mag create table sa PHP.

$queryCreateUsersTable = "CREATE TABLE IF NOT EXISTS `USERS` (
    `ID` int(11) unsigned NOT NULL auto_increment,
    `EMAIL` varchar(255) NOT NULL default '',
    `PASSWORD` varchar(255) NOT NULL default '',
    `PERMISSION_LEVEL` tinyint(1) unsigned NOT NULL default '1',
    `APPLICATION_COMPLETED` boolean NOT NULL default '0',
    `APPLICATION_IN_PROGRESS` boolean NOT NULL default '0',
    PRIMARY KEY  (`ID`)
)";

gawa ka lng po ng popup form pag hindi pa existing ung item type para maka gawa ng bagong table.

kung gusto mo namang mag update or delete ng atributes gamitan mo lng ng ALTER TABLE.

in short, you will have 1 database para sa items mo and 1 database para sa inventory mo. ung items kasi static na yan pag na entry na hindi na ma uulit kaya pwede mong gawing table ang type at attributes, ung dynamic database mo lng is ung inventory mo kasi pa bago2 ung amount ng item.

 

[flok1]

Hello mga database guru, ahm sino po dito familiar sa configuration ng mga memory allocation ng mysql database sa my.ini config file? di ko kasi magets yung mga parts nung config file if anyone knows please reply then I'll ask some questions thanks sirs