Re: Bit-wise foreign keys

On Sep 20, 2010, at 10:06 AM, Alban Hertroys wrote:

> Hey all,
>
> I'm tossing an idea around again, namely using bit positions and values as foreign key references. Let's start with a
bitof background information: 
>
> I'm currently parsing a log-file that I want to apply all kinds of statistical analysis to. This file contains lines
ofrecords of data, among which are some bytes of which each bit marks a certain truth-value. As an internal data-object
that'sjust dandy, but presenting it to, for example a user, or to query it for certain masks without having to delve
intothe definition of that particular bit-field it would be great to have a textual representation of each bit. 
>
> Let's say we have this byte, with the attached meanings:
>  pos  meaning
>   0   RED    (least significant bit)
>   1   GREEN
>   2   BLUE
>   3   FIRE
>   4   WATER
>   5   EARTH
>   7   AIR    (most significant bit)
>
> Now if I see a value of 0x05, I know that this corresponds to [RED, FIRE] and if I have a value of 0x41 I know that
I'min trouble as there's a bit set that has no meaning! 
>
> Reeks of a foreign key constraint, doesn't it? An odd one though, as one value can contain multiple bits and thus
referencesmultiple foreign values... 

Or references a single foreign value, if you have a reference table with all the valid bit combinations, which'd be
prettysimple to generate programatically for small numbers of combinations. 

  insert into foo (k integer, v text[]) values (0x21, '{"EARTH","GREEN"}';

You could also apply any other set of constraints you wanted in that way (Fire is Red, Water is either Blue or Green).

Or you could use a separate table to store the (record, enum) pairs with one entry for each set bit in each record,
withone foreign key constraint to the table of records and one to a static table of valid enum value types ((1, 'red'),
(2,'green'), (4, 'blue') ...). 

Cheers,
  Steve