0

I hit a roadblock with my database design. I have 3 levels of information:

  1. Table level
  2. Order level
  3. Item level

At the moment I implemented order-level as the main one. It is linked to table level through column table.

CREATE TABLE `orders` (
  `oid` int(11) NOT NULL AUTO_INCREMENT,
  `table` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `status` smallint(6) DEFAULT NULL,
  `items` longtext DEFAULT NULL,
  `group` int(11) DEFAULT NULL,
  PRIMARY KEY (`oid`),
  UNIQUE KEY `oid_UNIQUE` (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

All the information on the item levels is then concatenated in the items column which would look something like that:

1 Cola $5
3 Sprite $4
1 Pepsi $6

The problem I am having is partial changing of the status of one item in the order. For instance reducing quantity of Sprite from 3 to 1 when the order was only partially closed in that regard.

I have been thinking about changing the structure from order level to item-level (with additional columns for order FK as well as quantity and price). 99% of orders will be 1 item only so not sure the trade-off is worth it for that 1% of cases. This would create millions of database rows and significantly reduce performance long term for my mariadb instance. Another idea was to change the items column from plain text to json and manage order updates there.

Any thoughts on how to proceed?

jq1080
  • 47
  • 5
  • 3
    You need another table for the items of an order, not any text or JSON or other shenanigan columns. Normalize your schema. See ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) (Spoiler: Yes, it is.). – sticky bit Jan 15 '22 at 19:10

0 Answers0