unusual relation, binary aspect

Question

I have question very unusual for databases. I’m not a good English speaker, but good English reader :) so for explanation I’ll give you examples.

Part1

In MySQL, I have two tables with relations that I couldn't perform like primary-foreign key. Forward speaking, this is some "one to many" relation. This is examples of tables:

#1 – States
ID      ConcVal    Type     Time
1        14        1        05:30
2        13        1        06:30
3        10        2        07:00
4        13        2        08:50
5        10        2        09:00
6        13        2        09:40

ConcVal is less or equal 15 (in binary 1111) (actually I have 32 bit or even might more in future, but for simplify question it's only 4 bit). ConcVal - is something like superposition of Descriptions in binary. So ConcVal value may be the same for all Types, but meaning, that is described in second table, is different.

#2 - Descriptions
ID  Type    CVbits  Description
1   1       0       Some text for `type` 1
2   1       1       Some other text
3   1       2       Some other text
4   1       3       Some last text for type 1
5   2       0       Some text for type 2
…
8   2       3       Some last text for `type` 2

The relation between data makes by binary representation of ConcVal value: all bits that are “1” (excluding zero bit) relate to corteges of #2 by column CVbits and Type. Type in #1 and in #2 - the same thing. So:

ConcVal = 14 (1110) relates to corteges with CVbits {1;2;3};

ConcVal = 13 (1101) >> CVbits: {2;3};

ConcVal = 12 (1100) >> CVbits: {2;3}.

If talking about tables, the next one imaginary (actually, I did it) join can help understand my needs:

StateID     Type    ConcVal     (bin)       CVbits  DescrID
    1       1       14          (1110)      1       1
    1       1       14          (1110)      2       2
    1       1       14          (1110)      3       3
    …
    6       2       13          (1100)      2       6
    …
    6       2       13          (1100)      3       8

Part2 (very small)

I know that logic described above better implement in front-end, but I want test how much can provide DBMS, so I wrote UDF, called getCVbits, which return string (call this bitorderstring) contained position numbers of bits in ConcVal, something like that “,1,2,”, and query left join with clause “where getCVbits (a.ConcVal) LIKE concat(‘%,’b.CVbits,’,%’)”. So, as you purpose, that query execute too long. Something near 1,2~2,0 sec for 300~3000 rows. I guess, UDF is too slow. But if you want, I think I can give you real query.

Part3

What if I will write insert and update triggers which are used getCVbits and calculate needed string. This string I can store in #1. That allows querying much faster, but goes against data normalization.

Question:

Q1. Is storing computed bitorderstring are good way or maybe let user wait a little? =) (I guess, result set will be much more than I tested)

Q2. How can I join States to Descriptions without LIKE operator in case using getCVbits? Actually, I can return from UDF anything I need.

Q3. Are another ways to implement “relation” between tables... data?

UPDATED: So, like I promised, the results of 2 queries:

SELECT
    b.ID AS StatusID,
    b.Type as Type,
    b.CVTime AS CVTime,
    b.ConcVal AS ConcVal,
    CONV(b.ConcVal, 10, 2) AS BinStr,
    a.ID AS DescrID,
    a.CVbits AS CVbits,
    a.Description AS Description
FROM
    (Reasons a
    JOIN States b ON ((a.Type = b.Type)))

and #1

WHERE
    (a.CVbits <> 0)
        AND (a.CVbits <> 8)
        AND (a.CVbits <> 16)
        AND (1 << a.CVbits) & b.ConcVal

and #2

Like suggested Rick James:

An easier (an faster at run-time) solution would be make CVBits an integer and store the binary-equivalent value (1, 2, 4, 8, 16) then join ConcVal to CVBits using a binary AND operator.

WHERE
    (a.CVbits <> 0)
        AND (a.CVbits <> 8)
        AND (a.CVbits <> 16)
        AND (a.CV & b.ConcVal)

Not so different at ~4280 rows returned (duration/fetch):

#1 (1<<CVbits & ConcVal)    #2 (CV & ConcVal)
0.016 sec / 0.421 sec       0.015 sec / 0.687 sec
0.016 sec / 0.156 sec       0.000 sec / 0.296 sec
0.031 sec / 0.234 sec       0.016 sec / 0.171 sec
0.016 sec / 0.218 sec       0.016 sec / 0.359 sec
0.016 sec / 0.172 sec       0.016 sec / 0.125 sec

instead (1.794 sec / 78.235) sec when i use UDF and LIKE.

Answer 1

WHERE (1 << CVbits) & ConcVal
  AND CVbits != 0    -- (to "exclude the zero bit")

Answer 2

Q1. Is storing computed bitorderstring are good way or maybe let user wait a little? =) (I guess, result set will be much more than I tested)

MySQL supports columns of type bit. These only use one bit each, in sets of 8, as you would expect. This will give the same disk and memory density as you're currently getting, but with much easier query writing.

Q2. How can I join States to Descriptions without LIKE operator in case using getCVbits? Actually, I can return from UDF anything I need.

@Rick Has it: you need to do binary maths on tblDescriptions.CVbits. An easier (an faster at run-time) solution would be make CVBits an integer and store the binary-equivalent value (1, 2, 4, 8, 16) then join ConcVal to CVBits using a binary AND operator.

Q3. Are another ways to implement “relation” between tables... data?

Yes. Normalise your design. ConcVal breaks first normal form because it holds many values. Create a new table which contains only tblStates.ID and tblDescriptions.ID.

And please, please stop prefixing your table names with 'tbl'.