Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re^4: key value in text format

by pwagyi (Monk)
on Nov 07, 2019 at 02:05 UTC ( [id://11108414] : note . print w/replies, xml ) Need Help??


in reply to Re^3: key value in text format
in thread key value in text format

The link method to handle key/value pair is a good idea. but I think I might just go with JSON (column) for all key/value pairs. What is the trade-off between the two approaches?

Replies are listed 'Best First'.
Re^5: key value in text format
by The_Dj (Sexton) on Nov 11, 2019 at 02:56 UTC
    Searchability.

    using a linked table, you can do
    SELECT * FROM main_data WHERE ROWID IN ( SELECT link_id FROM extra_data WHERE key1 = ? AND value1 LIKE ? )

    Consider also:
    do you want to put a storage method (JSON) inside a different storage method (SQL)?
      Modern databases understand JSON and can search inside the structure, see e.g. MariaDB or PostgreSQL.
      map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
        Modern databases understand JSON and can search inside the structure

        But is that as efficient as "classic" scalar datatypes, like strings or integers? Any benchmarks?

        Alexander

        --
        Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)