in reply to OT - SQL choosing a schema for index tables

This seems like an ideal use for BIT() type fields; assuming none of your multi-value fields can have more than 64 possibilities.

Then in your program, you define constants for each of the bits in each of the fields:

use enum qw[ BITMASK:Usage_ cider juicer cooking eating display_and_th
+row_away ];
use enum qw[ BITMASK:Flowers_ spring summer autumn winter ];
use enum qw[ BITMASK:Ripe_ spring summer autumn winter ];
use constant Any => -1;

...

my $sth = prepare( 'select id from trees where usage = ? and flowers =
+ ? and ripe = ?;' );

$sth->execute( Usage_cider|Usage_juicer, Flowers_spring|Flowers_summer
+, Any );

[download]
With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
"Science is about questioning the status quo. Questioning authority". I knew I was on the right track :)
In the absence of evidence, opinion is indistinguishable from prejudice.
I'm with torvalds on this Agile (and TDD) debunked I told'em LLVM was the way to go. But did they listen!

Replies are listed 'Best First'.
Re^2: OT - SQL choosing a schema for index tables
by erix (Prior) on Aug 28, 2015 at 14:49 UTC

    Of course it is possible --- but now the program is needed to explain those bit-values that are in the database. I count that as a disadvantage.

    Does not sound like a good idea to me. It would be more acceptable when the mapping could be done inside the database (thus documenting these bit-fields). But even then this does look like over-engineering to me. (There are 200 rows, the OP has said).

[reply]
      It would be more acceptable when the mapping could be done inside the database

      It's pretty trivial to set up secondary table(s) that maps bits to strings. Done right, they could be joined to produce human readable output.

      With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
      Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
      "Science is about questioning the status quo. Questioning authority". I knew I was on the right track :)
      In the absence of evidence, opinion is indistinguishable from prejudice.
      I'm with torvalds on this Agile (and TDD) debunked I told'em LLVM was the way to go. But did they listen!
[reply]

In Section Seekers of Perl Wisdom