DB Performance

krontrex · #1 (**permalink**) 09-28-10, 05:08

Dear All,
I have an object that is represented with a set of key-value pairs. I am considering two approaches:
1) to make two tables; one for the object other for key-value pairs and then to connect them with an association table or
2) to put all key value pair in a table that represent object.
The first approach avoids redundancy and the second should be more performant.
What is a solution for this problem? When to use approach 1 and when 2? If I apply index in the approach1 are they are going to provide better performance?
Thankfully

andrewst · #2 (**permalink**) 09-29-10, 09:08

By option 2 do you mean that for an object like Employee with key-value pairs like(ID=123, Name=John, Salary=100, HireDate=01-APR-2010) you would create a table like this:

Code:

create table employee 
( id integer primary key
, name varchar(10)
, salary number
, hiredate date
);

?

If so, it would get my vote!

krontrex · #3 (**permalink**) 09-29-10, 09:31

Thanks for the reply,
I am looking for the solution of the problem that emerges in case that the number of key value pairs is not known. Is there any way to define a table object in run time?
The second question would be is it always more performant to avoid association tables even on the costs of data redundancy?
Thanks

shammat · #4 (**permalink**) 09-29-10, 09:41

Quote:

Originally Posted by krontrex

I am looking for the solution of the problem that emerges in case that the number of key value pairs is not known. Is there any way to define a table object in run time?

Which DBMS?
In Postgres you could use the hstore datatype:

Code:

CREATE TABLE object_attributes
(
   object_id     integer        NOT NULL PRIMARY KEY,
   object_name   varchar(100)   NOT NULL,
   attributes    hstore         NOT NULL
);

INSERT INTO object_attributes
(object_id, object_name, attributes)
VALUES
(1, 'something', ('color => blue, size => XL'));

INSERT INTO object_attributes
(object_id, object_name, attributes)
VALUES
(2, 'another thing', ('resolution => "1900x1600"'));

krontrex · #5 (**permalink**) 09-29-10, 10:13

Yes Postgres,
Thanks I have not heard of hstore.
Again about performance?
Why is the solution with an association table less performant? Does it help when I use indices?
Thanks

pootle flump · #6 (**permalink**) 09-29-10, 10:40

An association table is used to implement a many to many relationship. For it to be applicable in this instance would imply you intend to store every possible key value pair combination in one table and associate these with objects in the association table.

Is that what you had in mind?

krontrex · #7 (**permalink**) 09-30-10, 04:33

Yes exactly, and my question is related to performance.
As I could see, the solution to save key value pairs in respective object table is much more preferred than to store all pairs in one table which will be associated through a table with object tables.
Why?
Is it because it employs two tables less?

pootle flump · #8 (**permalink**) 09-30-10, 04:39

I didn't say one was preferable over the other. With the association table you do at least have something approximating relational integrity. I also don't know anything about Postgres. If there is a significant number of rows then table\ index partitioning would help keep the B-Tree level down, but I don't know if postgres supports this or if you would have to roll your own. Or, indeed, if it would be necessary.

krontrex · #9 (**permalink**) 09-30-10, 05:48

What about other DBMSs?
So, sometimes is not so effective to stick to the normalization rules. Is there any best practice with the respect to this problem?

andrewst · #10 (**permalink**) 09-30-10, 06:14

Quote:

Originally Posted by krontrex

What about other DBMSs?

For Oracle I have seen it suggested that a solution is to have an XMLTYPE column to contain such arbitrary name/value pairs - search for XML here. Tom Kyte, the Oracle guru who runs that website, calls your requirement the "funky data model", and he's about as keen on it as I am!

pootle flump · #11 (**permalink**) 09-30-10, 06:26

SQL Server also supports XML. Note though that the use of XML is not in violation of normalisation. The XML datatype is still atomic to the database engine - shredding would be done by an application layer.

andrewst · #12 (**permalink**) 09-30-10, 07:19

Quote:

Originally Posted by pootle flump

SQL Server also supports XML. Note though that the use of XML is not in violation of normalisation. The XML datatype is still atomic to the database engine - shredding would be done by an application layer.

True, but it does make querying a lot harder when you want to filter on values that are held somewhere in the XML data.