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 (Scribe) 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 ?
  )

    [download]

    Consider also:
    do you want to put a storage method (JSON) inside a different storage method (SQL)?
[reply]
[d/l]
      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]
[reply]
[d/l]
        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". ;-)
[reply]

In Section Seekers of Perl Wisdom