CODEWITHSUNDEEP
phpmysqlsqlmongodbdatabase
Jordan Ramstad
Jordan Ramstad

Reputation: 179

Blending Relational with None-Relational (MySQL and MongoDB)

This has been talked about before but I have yet to come across a clear answer, its just roughly described as fire and water and left at that (from my research).

Relational and None Relational databases are very different, but they both pull data, for my project I plan to use a None Relational database, however this will be installed in many places and some only have access to MySQL (then later moved).

So is it possible to force MySQL into a kind of None Relational mode? I have used a schema that sort of mimics it but it still holds aspects of a relational database that so far I have not been able to overcome (overly dependent on ID's and such, leading to syntax/data structure being messy).

So is there a magic library that will do this?

Here is a rough outline of my database schema:

1 table is "meta" it contains and id, along with type and date and such, commonly searched fields that are universal basically.

1 table that contains "data" this has multiple rows for each "column". It cannot be done through a join so its 2 queries to get the data.

CREATE TABLE `meta` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL,
  `state` tinyint(3) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

CREATE TABLE `data` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `meta_id` int(11) unsigned NOT NULL DEFAULT '0',
  `index` varchar(255) NOT NULL,
  `value` longtext NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

As you can see not easily searchable unless its by id/date or something, it also requires PHP to take up a lot of the slack for ordering and such. Not what I am really worried about though but to do an actual search it would need to dump the entire database and chew through it.....

What kind of MySQL schema (or concept) could best replicate a None Relational model (and still handle search reasonably)?

Upvotes: 0

Views: 70

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562911

First, there is no such thing as magic.

You have reinvented the Entity-Attribute-Value design. This is a non-relational design. I've written about this before, but in brief: you end up having to implement in application code many features that you take for granted in an RDBMS, like constraints and data types.

This is related to the concept of the Inner-Platform Effect:

The Inner-Platform Effect is a result of designing a system to be so customizable that it ends becoming a poor replica of the platform it was designed with. This "customization" of this dynamic inner-platform becomes so complicated that only a programmer (and not the end user) is able to modify it.

If that's the type of work that you would like to spend your time doing, then go for it.

My preference is to use MySQL for relational data, and use a non-relational data store for non-relational data. One can access both databases from the same application.

its just roughly described as fire and water and left at that (from my research).

I think of it more like fire and marshmallows. If you know what you're doing, you can make one of the best treats in the world. Or you could end up holding a stick covered in a charred, sticky mess.

Upvotes: 2

Related Questions