Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Liran.

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

Last updated: February 28, 2012 - 10:41 am UTC

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 Tom 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

Rating

  (2 ratings)

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

Comments

Thank you

Liran Zelkha, February 28, 2012 - 7:49 am UTC

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

Rajeshwaran, Jeyabal, February 28, 2012 - 9:36 am UTC

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
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.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.