Results 1 to 15 of 15

Thread: DB Performance

  1. #1
    Join Date
    Sep 2010
    Posts
    40

    DB Performance

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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!

  3. #3
    Join Date
    Sep 2010
    Posts
    40

    reply

    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

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Quote Originally Posted by krontrex View Post
    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"'));

  5. #5
    Join Date
    Sep 2010
    Posts
    40
    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

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2010
    Posts
    40
    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?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2010
    Posts
    40

    Best practice

    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?

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by krontrex View Post
    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!

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by pootle flump View Post
    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.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by andrewst View Post
    True, but it does make querying a lot harder when you want to filter on values that are held somewhere in the XML data.
    Isn't that the point in using XML inside a database?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by Pat Phelan View Post
    Isn't that the point in using XML inside a database?
    Of course!

  15. #15
    Join Date
    Oct 2010
    Posts
    1
    Thank you all the answers. Very useful.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •