What are good ways to store decorated objects in a relational database without adding a field for every possible property?

Question

Suppose I have an object, I use a decorator to add properties to this object, and then I want to store the object and its properties in a relational database, and be able to run queries on these stored properties.

For example, say I've got a database with kitchen equipment. Base properties would include things like make, model, and description. Of the pieces of kitchen equipment, some use electricity, some use gas, some use water, some need a drain, some need fire protection, some need routine maintenance, etc. These properties are generally not Boolean flags, but are more of a rich collection of classes themselves. So, if I want to keep all of my "equipment" in one table, I've got to find a way to store all of these properties.

I'm using a decorator to add properties to objects instead of using inheritance to reduce the number of classes, as I don't want to have 2^(number of properties) classes.

Two things I've come up with so far are table per property and field per property.

Table per property has many more joins to reconstruct the object and field per property has a lot of empty fields for many objects, and requires adding a field whenever I introduce a new unique property. Is there some (sane) way to address this other than to use one of the two methods mentioned above? I know that this can be done in some NoSQL databases, this question specifically concerns relational databases.

Answer 1

If you have a lot of properties to attach to an object, and don't want to create columns, then you'll have to store the properties in a single column that can be searchable - either via 'like' or freetext search features or by using a complex column type that your DB may or may not support (ie PostgreSQL's JSON type).

I have had success with storing a set of on/off flags in a single column by storing a human-readable TLA code for each, if the code was present then the flag was set. This was easily queried using a like clause (assuming each code was unique).

Answer 2

It is not clear in your question whether you need to use these dynamic properties in your database queries. I'll assume you won't.

With this assumption, I can actually point you to an implementation I've used in the past, although I'd add a disclaimer: in these days, you really shouldn't do this unless you're really stuck to your current DB and cannot, at all, move the persistence of this collection of objects to a NoSQL DB, which is where it seems to belong.

Well then, to the answer: You can make use of an object serialization engine, such as the one in Java (maybe you're using another language, but I'm sure it has some way of doing the same thing), then create a database table where you store the serialized objects. Then, in order to be able to query this table, you have the following options:

1. If you have common properties between all objects, you can create a column for each property that is needed in queries, then populate them based on the object being serialized whenever you have an insert or update.

2. If you need to query by using some properties that only exists in a few objects, you can create another table with a foreign key to the unique ID of the object in the original table, then add the columns needed for querying.

You can mix the options above as you wish - in my case, I ended up with one table just for storing the serialized objects, given unique IDs, then multiple tables for querying. There was some duplication of data, but it was done based on performance measurements of the many reports that had to run on these objects. Your mileage may vary.

In the end, whichever query structure you choose will give you a relatively good mix of the advantages of relational DB queries while retaining the object structure used by your program, since the deserialization process should recreate the object (or object graph, given the use of decorators), just as it was before being serialized.

However, I feel like I should stress this again: I do not believe that this should be done in this day and age, unless you have real economic/political/whatever reasons to do so.

Answer 3

As others have mentioned it would depend upon what you need to be able to query on. Do you need indexes? Aggregates? Foreign keys? Joins?

Personally, would go with the approach of a single table with individual fields for important attributes and the rest I would store in an xml doc in the same row. Xml is flexible and therefore is ideal for optional attributes.

Answer 4

Most RDBMS now support JSON data type. you may have one column with JSON datatype and store the JSON data. There are SQL functions that support JSON like ISJSON, JSON_QUERY, JSON_OBJECT, JSON_VALUE, etc.

https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html https://learn.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql?view=sql-server-ver16

In my experience, I save the JSON data. And then generate hash and save it a new column. This hash helps in retrieving the data faster for exact matches.