many to many relationship, bit wise comparison versus linker table

Question

A database table, lets say burger, contains a field that stores bits as an integer value, so it has value of either 1,2,4,8,16 ...

A secondary table contains the values 1 = cheese, 2 = tomato, 4=egg, 8 = lettuce, 16 = mayo

Bit wise comparison then enables any burger to have any combination of fillings (for the uninitiated a burger with val 24 would have mayo and lettuce(16 + 8), and a burger with val 5 would have cheese and egg, etc).

As far I can see this is not a particularly good idea, you are limited to a relatively small range of different burger fillings (64?). The only merit is it saves space on the database as no need for a linker table.

However, I am taking over from a someone who was, generally, very good technically; so are there are any other benefits I am missing? As it seems to me there are two negatives: it limits the number of different fillings, and its weird/unusual (a hang up from the when memory was the main consideration ?).

Answer 1

The only benefit I see, is that it might make it relatively easy to return a comma-delimited field containing all the burger toppings and other data all in one row. Offhand, I don't know how that would be done though. I would be interested to see that SQL if that's the case.

If the front-end program cached data from the secondary table and parsed the bitwise field after being returned it would eliminate a call to the database as well.

However, I don't see any other benefit. I would just use a linker table and be done with it, because the bit-packing, I think, just complicates the database design.

Answer 2

Using bitmasks this way, you will not be able to leverage the power of SQLs referential integrity checks. What happens if somehow I enter a value of 128? There is no such entry in your lookup table. With foreign key constraints properly imlemented, if I attempted to load a value that did not exist in the lookup table, I'd get an error and the value would (properly) not be loaded in the database.

It may be quicker and easier (and more interesting) to implement bitwise operators, but properly normalized databases can be a lot easier to support and maintain over time.

Answer 3

You could get around that solution by adding a table that would look like:

Table: Fillings
Id  Cheese Tomato Egg Lettuce Mayo
 0     N      N     N     N     N
 1     Y      N     N     N     N
 2     N      Y     N     N     N
 3     Y      Y     N     N     N
...
31     Y      Y     Y     Y     Y

then you can say:

select
    count(1) as WithCheeseButNoTomato
from Burger as b
join Fillings as f on f.Id = b.FillingId
where Cheese = 'Y'
  and Tomato = 'N' ;

Any supposed benefits are in the front end filling selection or in the application layer.