Optimal DB structure for additional fields entity

Question

I have a table in a DB (Postgres based), which acts like a superclass in object-oriented programming. It has a column 'type' which determines, which additional columns should be present in the table (sub-class properties). But I don't want the table to include all possible columns (all properties of all possible types).

So I decided to make a table, containg the 'key' and 'value' columns (i.e. '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 to contain the available 'key' values.

But there is a problem with such architecture - the 'value' column should be of a string data type by default, to be able to contain anything. But I don't think converting to and from strings is a good decision. What is the best way to bypass this limitation?

Answer 1

The only workaround (while retaining your strucure) is to have separate tables:

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

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

Answer 2

One common approach is having the key-value table contain multiple columns, one for each data type, i.e. StringValue, DecimalValue, etc.

Just know you're trading queryability and performance for a database schema you don't need to change. You could also consider ORM mapping or an object database.

Answer 3

How about this instead... each sub-type gets its own DB table. And the base/super table just has a varchar column that holds the name of the sub-type DB 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 4

You could have a per type key/value table. The available table would need to encode the availability of a specific key/type pair to point to the correctly typed key/value table.

This seems like a highly inefficient architecture in for a row based relational databases however.

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

Answer 5

The design you're experimenting with is a variation of Entity-Attribute-Value, and it comes with a whole lot of problems and inefficiencies. It's not a good solution for what you're doing, except as a last resort.

What could be a better solution is what fallen888 describes: create a "subtype" table for each of your subtypes. This is okay if you have a finite number of subtypes, which sounds like what you have. Then your subtype-specific attributes can have data types, and also a NOT NULL constraint if appropriate, which is impossible if you use the EAV design.

One remaining weakness of the subtype-table design is that you can't enforce that a row exists in the subtype table just because the main row in the superclass table says it should. But that's a milder weakness than those introduced by the EAV design.

edit: Regarding your additional information about comments-to-any-entity, yes this is a pretty common pattern. Beware of a broken solution called "polymorphic association" which is a technique many people use in this situation.

Answer 6

Thanks for the answers. I'll explain a little bit more specifically what i need. There's a need to program a blog+forum website, and I've been looking at the WordPress DB structure.

There's a strong need for the ability to place comments to any kind of 'object', like a blog entry, or a video file attachment to it. The above DB structure being very easy to scale and to fulfill all our needs was the reason of its choice.

But that's not late to change it, cause this is in stage of early engineering. Also our model smells now like a completely tree-hierarchy based DB. For now I'll accept Bill Karwin's and fallen888 answers, but maybe I'm going in a totally wrong direction?

Answer 7

about the user being able to add a new field to the table:

I admire all these people making comments.

I used to be interested in this kind of thing a few years ago, but have written little code recently (apart from a little bit of PHP and MYSQL).

I think it's fine if you want to keep going - you may end up with something new.

Sorry to pour any cold water on the scheme - I admire your efforts. My personal belief is that if you go far enough in this direction, you will end up with a system that interprets more of natural language than SQL does. (Around 1970, SQL was actually spelt Sequel, and it actually stood for "structured english query language", but after they standardized it in the 1970's - I think someone said that Oracle was the first commercial implementation, 19079, the "English" got dropped off, because I guess they decided that it was only a tiny subset of English.

I have run out of steam in this area, because I haven't got a job. Without an easy job that pays the bills, where I can experiment with these ideas, it's a bit hard to concentrate on this area.

Best wishes to all.

Answer 8

sorry, I wrote 19079 above, I meant the year 1979. Oracle got their first contract writing a database for the CIA.