EAV vs. Dynamic Columns

Phil

Mar 11, 2014 17:09

@MichaelT I have a question for your answer here: programmers.stackexchange.com/a/231943/7935

I see how a lot of your downsides are pretty big, though I wonder about a couple of them. For example, the clean upgrades bit and the users adding a column name that's a reserved word.

If your database is designed like this, where your attribute table only has one single column, i don't see upgrades being a problem simply because you'd never upgrade that table. Would you say that's a naive assumption on my part?

user55340

+-------------+   +------------+
|thing        |   |thing_attr  |
|-------------|   |----------- |
| id          |<--+ id (pk&fk) |
| type        |   |            |   <-- room for more columns
| desc        |   |            |
+-------------+   +------------+

Phil

ah yeah, sorry. i'm in IRC mode, not stackexchange mode

user55340

You've still got the meta-programming in order to find out the information about the columns.

Phil

right, yeah that's definitely a downside

i don't question that part

however i was also thinking that the extra columns could be set up in a configuration file by a power user, and we can remove much of that meta programming by referring to the config file

user55340

You might be able to get away with it. It still feels awkward.

user55340

Mar 11, 2014 17:15

Question is - what do the extra columns map to in your model? How do they map to it?

Phil

the extra columns would just be extra information about the thing. there wouldn't be any foreign keys or constraints or anything

just one line in the attribute table maps directly to one thing in the thing table, and a simple join would give us all the custom info we need about that thing

user55340

Say you're using Java. I'm using hibernate and have a class that has annotations that map column names to particular fields within the class.

user55340

But if you've got something in the model that I don't control, I can't modify the class to add the new fields... so I can't get at it. I've got to map it to something to be able to access it in Java.

Phil

ah. yeah one thing you correctly assume in your answer is that i've more-or-less abandoned the ORM

in particular i'm using dapper

user55340

And if I add a new field in the .java file, it needs to be recompiled and deployed to work with the new data.

Phil

Mar 11, 2014 17:19

which allows me to write my raw SQL

and i can manually control how the results of a query are materialized into model objects

user55340

And you're doing things like 'select * from thing_attr'?

Phil

so i'd have a public IDictionary<string, object> GetAttributes() or something of the like

select * is an option

or i can get the column names from the config file

user55340

But now you've got an Object object... that is not type safe to be throwing around.

Phil

correct. another downside.

however usually object.ToString() is sufficient

i'll probably also have the type information in the config file, so while it's not type-safe, i can still work with it

and that problem really isn't avoided by the EAV model either

user55340

I'm not going to say its much better, but Postgres has a json data type and functions to work in the data in the column: postgresql.org/docs/9.3/static/functions-json.html

user55340

Mar 11, 2014 17:22

The thing is that while the EAV is far from ideal, it is understood and does solve some problems.

Phil

that's nice. i imagine it would be hard to query by json objects though

user55340

One big one is that you don't need DDL to be able to manipulate it.

user55340

@Phil Its non-trivial, but it exists.

user55340

(and its also database specific... so give up that if you want to be able to deploy to Oracle and Postgres)

Phil

i was also thinking that since i've abandoned the ORM, it might be relatively easy for me to create the necessary DDL for the different vendors and just use abstraction there

user55340

Mar 11, 2014 17:24

But then, creating DDL is also database specific... so thats a wash too.

Phil

so i'd have an ICreateTableCommand interface or something like that, and then the MSSQLCreateTableCommand and PostgresCreateTableCommand, etc.

user55340

In the applications that I've worked on where there are DBAs who look at what you are doing, there are 3 users: schema owner for doing DDL changes, write user for the web apps that do writes (sometimes one user/webapp) and read user for things that need to do reads (again sometimes one user/app).

user55340

No program user ever gets DDL access.

Phil

interesting

user55340

There's no reason for a webapp to be able to issue the command "alter table" ever.

user55340

Mar 11, 2014 17:27

And once they have schema access, they can do anything they want to the schema.

Phil

hm. that actually sounds like the biggest problem so far

the clients i'm targeting will probably be in a bigger, more formal IT environment than i currently am

user55340

By restricting the user to what they need and only what they need, if something is hacked there is as small a surface as possible to be exploited.

Phil

i wonder if it would be reasonable for an admin to create the database with software i give them, and then the actual web application wouldn't touch the schema after that

user55340

Sure, you hacked the webapp that handles customer facing bug tracking. Good for you. But you know what, you can only read 3 tables in that database. No write access.

user55340

@Phil Possibly. Though you could also use an EAV and give them an admin ability to modify the attr table with that webapp...

Phil

Mar 11, 2014 17:31

true. the EAV still doesn't sound like a great option... i may have a lot of custom attributes for a lot of things...

user55340

Consider also... sometimes people like to run sql against your application (I did it with bug tracking apps I've deployed) to be able to write my own reports. I'd hate to have my query break because someone added (or removed) a column from a table.

Phil

but it sounds like the safest option

user55340

redmine.org/projects/redmine/wiki/DatabaseModel

Phil

yeah

oy

user55340

Notice the Custom fields in the upper left corner.

user55340

Mar 11, 2014 17:32

custom_fields and custom_values is that EAV part.

user55340

There's an admin tool to be able to modify custom_fields. And then the data is stored in custom_values

user55340

@enderland +15, 48 views. Holding steady at about +1 for every 3 views so far.

Phil

it's good to see a real-world example. still looks hairy, but i don't think i have much of a choice.

user55340

Its hairy to a point in that its a well understood hair.

Phil

@MichaelT thanks for letting me pick your brain a bit.

user55340

Mar 11, 2014 17:37

A DBA will look and go "ug, an EAV table..." but they won't go "WTF is this table with only a foreign key and no columns?!"

user55340

@Phil all that said, make sure you read simple-talk.com/opinion/opinion-pieces/bad-carma

user55340

And an important takeaway from that is "you job is to write useable software, not usher in a new world of another database pattern".. sure you can do that... but thats not your job.

Phil

word. i'd still like to try my idea, though on a personal project or something. definitely not ready for DBAs in enterprises

user55340

Proof of concepts are a great thing to do and personal projects a great way to do it... if nothing else so that years down the road when some young upstart kid (;-) says they've got a great new idea, you can point to things and say "it didn't work quite so well" or "yep, but keep these things in mind."

The Whiteboard

Participants