"It should be pretty simple," David M naïvely stated, "just look in the Agent_ProductLines table, right?"

"Uhhh," David's coworker, James, replied in a slightly condescending tone, "no." David was starting to get used to such responses. Nothing in his new job was "pretty simple" to simple to do.

"Okaaay... so how exactly can we tell if a particular Agent is allowed to a given Product Line"

James groaned. "Well as you know, the original database developer, wasn't really a fan of normalization, the relational model, or just plain simple common sense. In this case, he did not want to 'waste space' by creating a table just to store the Agent-Product Line relation."

David sighed. He knew exactly what was coming up: some stupid comma, tilde, asterisk delimited string on the Agency table.

"Oh it gets worse," James replied, "see, take a look at this column, the NVARCHAR(2000) one called strProductLines?"

David took a look. He was confronted with something far worse than a delimited string...

012-3100000100110000001000000103000001001100000-1011010101---0010011000000100000001000001001---00000500000010600010011000000100000001000001001100000010

"What the--"

"Here," James said, cutting him off, "let me show you how it works." He grabbed a pen and a pad of paper, and started to draw a little table ...

ID 1 2 3 4 5 6 7 8 ...
Value 0 1 1 - 3 1 0 0 ...

"Wait," David said, "you're telling me, the index of each character corresponds to the product line? And a one means that they can sell a product line?"

"Yes," James replied, "but it's a bit more than that. '0' means they can't sell, '1' means they can, '-' means there is no such product with that ID, and '2', '3', '4' - all through '9', all mean different things. Like, '3' means that the agent has received training on the product line, but has not been approved to sell, and so on."

David buried his face in his hands for a brief moment. "So how exactly can can I build this 'Find Agency by Product Line' feature?"

"Well," James winked, "it should be pretty simple."

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!