Search code examples
mysqldatabase-designnosqlrdbmsobject-oriented-database

Create schema-free (document-oriented) table in MySQL

We are working on a LAMP-based software with customer data as a central unit. The table with the customer data is supplied by various sources via the import interface. The problem is, the table must not have a fixed schema. It may be that row X has five columns and row Y has twenty columns.

Our previous solution was as follow:

CREATE TABLE `customers` (
  `id` int (10) unsigned NOT NULL auto_increment,
  `branch_id` int (10) unsigned NOT NULL,
  `type` tinyint (1) DEFAULT NULL,
  `visible` tinyint (1) DEFAULT NULL,
  `status` tinyint (1) NOT NULL DEFAULT '1',
  `2` varchar (255) NOT NULL,
  `3` varchar (255) NOT NULL,
  `4` varchar (255) NOT NULL,
  `5` varchar (255) NOT NULL,
  `6` varchar (255) NOT NULL,
  `7` varchar (255) NOT NULL,
  `8` varchar (255) NOT NULL,
  `9` varchar (255) NOT NULL,
  `10` varchar (255) NOT NULL,
  `11` varchar (255) NOT NULL,
  `12` varchar (255) NOT NULL,
  `13` varchar (255) NOT NULL,
  `14` varchar (255) NOT NULL,
  `15` varchar (255) NOT NULL,
  `17` varchar (255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `branch_id` (`branch_id`)
  FULLTEXT KEY `search` (`2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `11`, `12`, `13`, `14`, `15`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

This table had a large overhead because we did not know what kind of data will be stored in the table, so we had to create only varchar(255) columns. Not seen here is a second table in which we have stored the names of the columns. This table looks something like this:

id   col   name
====================
1    2     firstname
2    8     zip

As you can see, we have started with a table that contained only the basic columns. Then we checked each time a import or update was done if new columns are added or if columns are no longer needed. If this were the case, we have deleted the corresponding column or added new ones. In addition, we always had to adjust the additional table.

This solution was a compromise for us in speed, simplicity and logic. In our opinion the most obvious solution would be the use of a document-oriented DBMS like MongoDB.

Unfortunately, we are forced to work exclusively with MySQL. Is there a solution to create a schema-free table in MySQL? Or are there other approaches which would be more useful?

Solution

  • You should explore the EAV pattern. If you are using PHP, then there are some toolkits that could make your coding easier: