SQL object store -- best practices

Question

I'm looking for a method for storing JavaScript objects in a MySQL Style database.

I would like to have the database setup to handle objects, their attribues, and even object references.

Giving me the following grammar:

<object> := list of atributes

<attribute> := integer
            | string
            | boolean
            | object-reference
            | list of attributes

Is there a standart way, or best pratice, of doing this?

I would like to used the database layout to enable filtering based on atribute values.

@foampile: What i meant is a ecma-script attribute-bag, aka a JavaScript Object — Martin Kristiansen, Oct 18 '12 at 14:58
This is database platform specific. What kind of database? As an example, in T-SQL you store JSON in a standard varchar field then use special JSON functions on it https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16 — Nick.Mc, May 30 '23 at 00:22

score 1 · Accepted Answer · answered Oct 18 '12 at 20:02

The reason you are finding it difficult to store your object is because of Object Relational Impedance Mismatch

The object-oriented paradigm is based on proven software engineering principles. The relational paradigm, however, is based on proven mathematical principles. Because the underlying paradigms are different the two technologies do not work together seamlessly.

The impedance mismatch becomes apparent when you look at the preferred approach to access: With the object paradigm you traverse objects via their relationships whereas with the relational paradigm you join the data rows of tables.

If you are stuck with a relational database then you will have to perform mapping between the object model and the relational model. If you are determined to store your objects in the way you describe then you will probably have to change to an Object Database

I have read Agile Database Techniques which covers this topic but I'm sure there are many others. There's a lot of information out on the web too.

But lets say I wanna store json data, and I want to be able to search the individual atributes? No schema, no nothing... Just some json data. Some of the entries might have a `value` attribute, and if they do I want the once with this attribute with a certant value. Do object databases support JS-objects? — Martin Kristiansen, Oct 19 '12 at 08:43
@MartinKristiansen - I did a bit more searching and found there are also [Document-oriented database](http://en.wikipedia.org/wiki/Document-oriented_database) which can be used to store JSON data, you might also want to look at [CloudKit](http://getcloudkit.com/). If you **must** use a relational DB then the data layer will have to unravel your JSON data and put it in table columns for searching. — Tony, Oct 19 '12 at 14:48

score 0 · Answer 2 · answered Oct 18 '12 at 18:42

0

As far as I know mysql is a relational database. It does not support storing objects in the manner you need. You just serialize the object and store it. No operations like accessing attributes is possible. However apart from mysql I remember hitting other db engines on Google that handle objects. Object Databases on Wikipedia

answered Oct 18 '12 at 18:42

Ertunç

819
1
9
21

let me rephrase the question, how do I make a database layout that supports storing objects. – Martin Kristiansen Oct 19 '12 at 08:45
@MartinKristiansen [Have a look at this](http://stackoverflow.com/questions/3564024/storing-data-in-mysql-as-json) . I think you need to locate an engine that supports storage of JSON as objects directly. The accepted answer in the link mentions CouchDB. – Ertunç Oct 19 '12 at 08:59
Whatever you design with relational engines you will be facing the conversion of object into data row while saving, and data row into object while retrieving. – Ertunç Oct 19 '12 at 09:02

score 0 · Answer 3 · answered May 29 '23 at 23:11

Another possibility would be to create tables for each class. You can then reference those concrete object instances within the table by giving them a foreign key which is the id of the data set of another table.

for example, if you want to store users and addresses:

create table users (
id uuid not null,
first_name text,
last_name text,
email text,

primary key (id)
);

then you add the addresses:

create table addresses (
id int,
name text,
street text,
zip int,
town text,
country text,
user_id uuid,

primary key (id),
foreign key (user_id) references users(id)
);

Now the addresses belong to a particular user.

SQL object store -- best practices

3 Answers3