Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Liran.

Asked: February 28, 2012 - 1:36 am UTC

Answered by: Tom Kyte - Last updated: February 28, 2012 - 10:41 am UTC

Category: Database - Version: 11g

Viewed 1000+ times

You Asked

I have an application that needs to save data that is unstructured in nature (basically - a set of key-values for a specific device). I have 2 options. One, is to have a simple table with a key and value columns (and FK on the device_id), and index the key. The other is to create a XMLType in the device table, and store data there (the schema will be a key-value mapping in XML).

What's the best solution here (both from storage and performance perspective)?

and we said...

if there was a best way...

it would be the only way - why would anyone invent a "not the best way" way ;)

It depends. To me, many times, the best way isn't even listed here. You store them as columns - attributes - of some table.


But, assuming you won't do that (it would make it pretty darn easy to query - and efficient too) - here are some things to consider:

a) XML - you could text index that and use some pretty sophisticated searching on it. Not as good as columns in a table, but it works.

b) EAV (entity attribute value) model - getting all of the attributes for a given device will be very fast and require no parsing

c) EAV - querying this model will be painful.



I'd probably rank them like this:

a) do a proper data model, #1
b) EAV - if I didn't need to query the columns #2, else #3
c) XML - see b, either #3 or #2

and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Thank you

February 28, 2012 - 7:49 am UTC

Reviewer: Liran Zelkha from Israel

Wow. Thank you! I really appreciate this answer. Can't use columns - as the data model changes on runtime. I will go with the EAV model then. 10x!

EAV Model

February 28, 2012 - 9:36 am UTC

Reviewer: Rajeshwaran, Jeyabal

Tom:

EAV - if I didn't need to query the columns #2, else #3
Can you explain or provide some documentation link about this EAV model?

Tom Kyte

Followup  

February 28, 2012 - 10:41 am UTC

just plug

EAV

into my search to see what I've written about it, or google to see what others have.