Optimal database structure for an object of additional fields

Question

I have a table in DB (Postgres based) that acts as a superclass in object oriented programming. It has a column type that determines which additional columns should be present in the table (subclass properties). But I do not want the table to include all possible columns (all properties of all possible types).

So, I decided to create a table containing the columns "key" and "value" (for example, "filename" = "/ file" or "some_value" = "5"), which contain any possible property of the object, not included in the superclass table. And also made one related table containing the available "key" values.

But the problem with this architecture is that the default column must have a string data type in order to be able to contain anything. But I don't think converting to and from strings is a good solution. What is the best way to get around this limitation?

Answer 1

The design you're experimenting with is an Entity-Attribute-Value variant, and it has a lot of problems and inefficiencies. This is a bad decision for what you are doing, except in a pinch.

What could be a better solution is the description of fallen888: create a table of subtypes for each of your subtypes. This is normal if you have a finite number of subtypes, which sounds like yours. Then your subtype attributes can have data types, as well as a NOT NULL constraint if necessary, which is not possible if you use the EAV design.

One of the remaining weaknesses in the construction of a subtype table is that you cannot ensure that a row exists in a subtype table only because the main row in the superclass table says that it should. But this is a milder weakness than those that were introduced into the EAV design.

edit: Regarding your additional information about comments on any object, yes, this is a fairly common template. Beware of a broken solution called a "polymorphic association" that is used by many people in this situation.

Answer 2

How about this ... each subtype gets its own database table. And the base / super table just has a varchar column that contains the name of the DB subtype table. Then you can have something like this ...

 Entity ------ ID Name Type SubTypeName (value of this column will be 'Dog') Dog --- VetName VetNumber etc 

If you don't want your (sub) table names to be varchar values ​​in the base table, you can also just have a SubType table whose primary key will be in the base table.

Answer 3

The only workaround (while maintaining the structure) is to have separate tables:

 create table IntProps(...); create table StringProps(...); create table CurrencyProps(...); 

But I do not think this is a good idea ...

Answer 4

One general approach is that the key value table contains several columns, one for each data type, i.e. StringValue, DecimalValue, etc.

Just be aware that you are trading queries and performance for a database schema that you do not need to change. You can also consider mapping an ORM or a database of objects.

Answer 5

You may have a key / value table for each type. An accessible table will need to code the availability of a specific key / type pair to indicate a correctly entered key / value table.

It looks like an extremely inefficient architecture for row-based relational databases.

Perhaps you should take a look at a column-oriented relational database?

Answer 6

Thanks for answers. I will explain a little more specifically what I need. There you need to program a blog + forum, and I looked at the structure of WordPress DB .

There is a strong need to post comments on any type of “object”, such as a blog post or attach a video file to it. The reason this database structure is very easy to scale and to meet all our needs.

But it’s not too late to change it, because it is in the early development stage. In addition, our model now smells like a tree-based hierarchy. At the moment, I accept the answers of Bill Carvin and fallen888 , but maybe I will go in the completely wrong direction?

Answer 7

that the user can add a new field to the table:

I admire all of these people commenting.

I was interested in this view a few years ago, but recently I wrote a little code (except for a bit of PHP and MYSQL).

I think this is normal, if you want to keep going - you can end up with something new.

Sorry that you spilled any cold water according to the pattern - I admire your efforts. My personal belief is that if you go far enough in that direction, you will get a system that interprets more natural language than SQL. (Around 1970, SQL was actually written by Sequel, and it actually meant "structured English," but after they standardized it in 1970, I think someone said that Oracle was the first commercial implementation , 19079, received "English" because I think they decided that it was just a tiny subset of English.

In this area I have run out because I do not have a job. Without the easy work that pays the bills, where I can experiment with these ideas, it's a little difficult to focus on this area.

Best wishes.

Answer 8

Sorry, I wrote 19079 above, I had in mind 1979. Oracle got its first contract, writing a database for the CIA.