So there I was in Camden High Street (which was weird because I live in Devon) posting a letter and minding my own business when a unicorn came along carrying Gandalf the Grey (who was actually my mum in a beard, bygones….) and he spake unto me.
“Young Wizard, have you never thought of using a bit field to represent related data within a database?”
The question threw me, In part because I really like the look of the giant sized marshmallow that Gandalf was now devouring, in part because I wasn’t aware that Gandalf had any interest in RDBS techniques but mainly because I hadn’t ever considered it
Of course, I’d love to say that this was how the idea really came to me, after all if the idea proved to be rubbish I could just say ‘Gandalf told me to and it was just a dream anyway!!!’ The truth is of course more mundane, a late breaking bug report (escalated immediately in my mind to a change request) that changed some relatively simple logic into a much more complex idea. So Gandalf and the bug got me thinking….
The basic premise is that I have a master table of items, lets call them ‘Shops’. Now each Shop can have a list of services that it can provide, these reside within a table called ‘Services’. Traditionally you could link these together using a ShopServices Linking table but in this particular instance it is overkill, I have only a few items and I dont want the complexity of adding a new table just to represent a join that used to be inferred by two tables. Instead I elect to add two new integer columns to the existing data tables.
- To the Shops table I add a new column called ‘Services’ this contains the logical bitwise AND values for the selected services.
- To the Services table I add a new column entitled ‘BitValue’. This contains the integer representation of the individual bit associated with this service
So to the data, my Shops table Looks like this:-
| ID | Premises | Service |
| 1 | London | 31 |
| 2 | Birmingham | 15 |
| 3 | Leeds | 16 |
And my Services table looks like this
| ID | Service | BitValue |
| 1 | Wheelchair Access | 1 |
| 2 | Lift | 2 |
| 3 | Changing Facilities | 4 |
| 4 | Restaurant | 8 |
| 5 | Servicing | 16 |
Now, I’m not going to explain how bitwise operations work, instead if you need to understand this the internet is your friend! As you can see from this data the Leeds shop is not so much of a shop as a service department, the Service’s offered are only ‘Servicing’ represented by the value 16, the London office with the value of 31 offers all of the services available and the Birmingham site offers everything bar servicing.
This just leaves the sql that would be required to join these tables together, this is in fact relatively simple using the logical & operator instead of a straight = within the join…
SELECT * FROM Shops AS SH INNER JOIN Services AS SVC
ON SH.Services & SVC.BitValue = SVC.BitValue
Voila….a simple master-detail relationship without the need for a linking table. Now I’m not one to argue with a wizard but I must say that I would find it hard to justify using this methodology over using a linking table. That said there are often scenarios where the unthinkable occurs and you find yourself using techniques that you otherwise would not, this maybe just the answer you are looking for in those circumstances.
So, Happy wizarding and remember, never toss a dwarf….