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 bit of background information:
>
> I'm currently parsing a log-file that I want to apply all kinds of statistical analysis to. This file contains lines of records of data, among which are some bytes of which each bit marks a certain truth-value. As an internal data-object that's just dandy, but presenting it to, for example a user, or to query it for certain masks without having to delve into the 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'm in 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 references multiple foreign values...

Or references a single foreign value, if you have a reference table with all the valid bit combinations, which'd be pretty simple 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, with one 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