Multivalue / Multivalued Fields vs Subtabling

A coworker of mine recently asked me how I would redesign a part of a our product's database if I were given the opportunity to start from scratch. Currently we have a security field that is a column in everyone of our user's records. It contains a string of Y's, N's, and R's. Y's stand for read and write, R's for read only, and N's for no rights at all. Each character's position is linked to a right for a particular section of our product. For example let's say our product was designed to manage a Burger Shop and there were there sections of the product to which we wanted to assign separate rights. Let's call these three sections burger inventory, payroll, and income. The way our product is currently designed we'd assign inventory to position 1 in the string, payroll to position two, and income to position three. The string would end up looking something like this:

RYN
(In this case this string would mean that the user had read rights to inventory, read and write rights to payroll, and no rights at all to income)

This design works nicely for some things and quite miserably for others. I'll start with the good:

1. When checking a right it is very easy to write a SQL statement to grab the contents of one column in a user's record.

2. It is very easy to update just one column in a user's record.

The Bad:

1. When writing code a programmer will often have to isolate one of the characters in this security string, which means they'll have to do something like so Substring(securityString, 2, 1). There are two problems with this:

a. For future programmers looking at this code, there's nothing there that tells the user which right the programmer was originally trying to examine. In fact, even if the programmer has access to the database and looks at the securityString column in the user's record they still won't be able to tell which right has been assigned to area of the product. This means that a separate list has to be maintained somewhere that the programmer can reference, so that he/she knows which string position is assigned to which right. This approach is cumbersome though and requires that all programmers and personnel update the list whenever a new right is added, even though the list is nonessential for the structure to function (it is essential for readability though).

b. Secondly, when even one right is update/modified the whole string must be updated which leaves room for error (i.e. accidentally changing one of the other string positions).

This leads me to the alternative design. In this design we subtable out the rights like so. First we create a master list of all the rights in a table called Rights. The rights table has a key field, a description field. In our case our rights table might look something like this:


Rights:

Key     |Description       

INCOME  |Income area right

PAYROLL |Payroll area right

INV     |Inventory area right






Then we create another table called USER_RIGHTS which has three fields. One with a foreign key to USER table's primary key, one with a value of Y, R, or N, and one with foreign key to the RIGHTS table's primary key. This table would look as follows:



L_USER |VALUE |RIGHT   

1      |R     |INV
1      |Y     |PAYROLL
1      |N     |PAYROLL



The above example gives us the same results as having a multivalue field populated with RYN. The great part about this design is that now it becomes very obvious as to what each right represents. It also becomes very easy to add new rights and to access existing rights. As a programmer, if I can't remember which code a particular area uses for it's right I can just look in the RIGHTS table and check the descriptions and hopefully I can find it there (assuming that good descriptions were used).



Drawbacks:



1. For every right added that another record for every user. For instance if you have 100 users and a 100 rights that's 10000 records in the USER_RIGHTS table. However this could be cut down considerable by not including the records that a user has no rights to, in the USER_RIGHTS table. For instance in the above example we stated the user had no rights to the income area by adding a record that said N for INCOME. Instead we could just take the absence of a record with the INC key in it, as meaning that the user has no rights to that particular area. This makes it even easier to add new rights to the RIGHTS table because not every USER_RIGHTS table would have to be updated.



2. When updating the USER_RIGHTS table if you are updating more than one right at a time, it would require more than one update statement. In fact, it would require one update statement for Y, R, and N (or a delete statement for N, if you decided that the absence of a record means that the user has no rights). In the multivalue design only one update is ever required because it is all stored in one field. The upshot to having to right individual update statements is that it becomes much more difficult to accidentally update rights for other areas.



All in all the subtable method is much more readable and much more flexible. At the end of our conversation we both decided that it was the best choice.

The located assembly's manifest definition with name xxx.dll does not match the assembly reference

A client was getting this error on a set of ASP.NET pages when I sent them a new compiled DLL for the C# code-behind (ASP.NET 1.1), and I wasn't sure what was going on. I then noticed something in the stack trace which clued me in to what they were doing wrong:

Assembly Load Trace: The following information can be helpful to determine why the assembly 'xxxold' could not be loaded.


=== Pre-bind state information ===
LOG: DisplayName = xxxold
(Partial)
LOG: Appbase = file:///c:/inetpub/wwwroot/xxx
LOG: Initial PrivatePath = bin
Calling assembly : (Unknown).
===

It was the xxxold in there that tipped me off to what they had done wrong. Apparently, as a way to back up the old DLL before putting the new DLL in the ./bin folder, they just renamed the old one xxxold.dll, which causes C# to throw this error. If fact, after I tested it, any DLL that isn't explicitly referenced in the DLL will cause an error if it is placed in the ./bin folder. To fix the problem all I had the client do was rename the DLL xxx.old instead. This worked perfectly.

12 Coins Problem with Non-Adaptive Weighing

I recently came across a problem on the net which went as follow:

You are given 12 coins, one of which is defective. The defective coin is either lighter or heavier than the rest of the coins. You are given a balance scale and are told to determine which coin is defective by using three non-adaptive weighs. This means that you cannot change which coins you decide to weigh based on the any of the previous measurements. Meaning there is nothing like this allowed:

IF COIN X weighs the same as COIN Y
THEN WEIGH COIN Z against COIN W

Instead you must basically come up with your plan of attack before weighing any coins, and then not stray from that plan once you've started.

Here is the solution that I came up with:

KEY:
L - Left side
R - Right side
U - Unused
H - Heavy
F - Light

Notes:
When I write something like this:
{1 2 3 4 5 6 7 8}:{H F}

It means that the defective coin could be 1..8 and I don't know if it is Heavy or Light yet

When I write something like this:
{1}:{F}

It means that the defective coin is 1 and it is Light.

/************FIRST WEIGH******************************************/

L {1 2 3 4}     R {5 6 7 8}     U {9 10 11 12}

At this point:
     IF unbalanced THEN {1 2 3 4 5 6 7 8}:{H F}
     ELSE {9 10 11 12}:{H F}

/************SECOND WEIGH*****************************************/

L {6 2 10 12}     R {5 1 7 11}     U {3 4 8 9}

At this point:
     IF {1 2 3 4 5 6 7 8} THEN
          IF balanced THEN {3 4 8}:{H F}
          ELSE IF unbalanced and in the opposite position
               as the first weigh THEN {6 1}:{H F}
          ELSE IF unbalanced and in the same position
               as the first weigh THEN {5 2 7}:{H F}
     ELSE {9 10 11 12}
          IF balanced THEN {9}:{H F}
          ELSE {10 11 12}:{H F}

/************THIRD WEIGH******************************************/

L {7 3 9 10}     R {6 4 5 12}     U {1 11 2 8}

At this point:
     IF {6 1} THEN
          IF balanced
               IF R was light (second weigh) THEN {1}:{F}
               ELSE {1}:{H}
          ELSE
               IF R is light THEN {6}:{F}
               ELSE {6}:{H}
     IF {9} THEN
          IF L is light THEN {9}:{F}
          ELSE {9}:{H}
     IF {5 2 7} THEN
          IF balanced THEN
               IF L was light (second weigh) THEN {2}:{F}
               ELSE {2}:{H}
          ELSE
               IF R is heavy THEN
                    IF R was heavy (second weigh) THEN {5}:{H}
                    ELSE {7}:{F}
               ELSE
                    IF R was light (second weigh) THEN {5}:{F}
                    ELSE {7}:{H}
     IF {3 4 8} THEN
          IF balanced THEN
               IF R was light (first weigh) THEN {8}:{F}
               ELSE {8}:{H}
          ELSE
               IF L is heavy THEN
                    IF L was heavy (first weigh) THEN {3}:{H}
                    ELSE {4}:{H}
               ELSE
                    IF L was light (first weigh) THEN {3}:{F}
                    ELSE {4}:{F}
     If {10 11 12} THEN
          IF balanced THEN
               IF R was heavy (second weigh) THEN {11}:{H} ELSE {11}:{F}
          ELSE
               IF L is heavy THEN
                    IF L was heavy (second weigh) THEN {10}:{H}
                    ELSE {12}:{F}
               ELSE
                    IF L was light {second weight} THEN {10}:{F}
                    ELSE {12}:{H}

Retrieve Data from Either the Form or Querystring Object

This may have been known by everyone for quite some time now but it's news to me. Apparently in classic ASP there is an alternative to using Request.Form("key") and Request.QueryString("key"). Instead, Request("key") can be used which will check both the QueryString and the Form with one line of code. If a form value and a querystring value get sent over with the same key, I'm not sure which one take precedence though. If you feel like trying this experiment, please send me the results (I'm too lazy to try it myself).

Detecting a loop in a linked list

I was asked an interesting computer science question today which left me stumped. The basic problem seems simple enough but it's more complex than at first glance. The idea is you have singly linked list that can either be standard linked list, a circular linked list (starts an ends in the same place), or a circuitous linked list (has a loop in it that doesn't necessarily go back to the start). How do you detect if a the linked list contains a loop without knowing the length of the linked list, and without being able to modify any of the data.

My initial thought was to just check and see if any of the nodes have null as their "next" value but this is an obvious pitfall because if it has a loop the code would run forever.

My next thought was to keep track of the memory location of each node and check to see if the next node's memory location has already appeared in the list, but this is not elegant, and could take a lot of memory (think of a long linked list).

After that I thought that at each position you could check the memory locations of each node up to the current one and compare them to the "next" node's memory location. This is essentially the same as my second idea but without having to store every node's memory location. This isn't a good solution either because it requires too many traversals of the list (aka SLOW!).

Next up I thought you could reverse the list and see if the the end node of the reverse list is the start node of the original list. This is good but it requires either of the two options:
a. Modifying the list (not allowed)
b. Making a copy of the list but in reverse order
Technically a could be remedied by then re-reversing the list but still then you'd have to go through the whole process twice.

Finally I had to ask for the answer and here is what I was told. Create two node objects one that is going to traverse the list quickly and one that is going to traverse the list slowly. The quick traverser moves two nodes ahead for every one node that the slow one moves. If the quick traverser ever catches the slow traverser then their must be a circuit. Why didn't I think of that? Such a simple solution.

Later I found a second answer that is pretty cool too. This one basically says always have one node to check held in memory. Basically start off with it being the second node and then check four more nodes to see if it gets selected again. If it doesn't then set the fourth node after it to the check node and check 8 nodes after that to see if any of them are the same. If they aren't set the eight node after the last check node to the new check node and then check 16 nodes after that to see if any of them are the same as the check node, and keep going until eventually one of the nodes that is traversed over is the same node as the check node, or a node with a "next" value of null is reached. I thought this solution was particularly interesting because it only requires one traverser.

I guess that's all I have on linked lists for now.

Unable to read the project file <project.csproj>. The system cannot find the path specified.

I recently received a new computer at work, after my old one blew up, and I was trying to setup my web project from SourceSafe and I kept getting the error listing in the title of this entry. I checked IIS to make sure that I had created the virtual directory correctly, and the path seemed to be fine. I also tried opening just the .csproj file instead of the entire .sln (there are several .csproj files in the solution I was working with), it opened just fine. Finally I came across an article that suggested opening Visual Studio and clicking File -> Source Control -> Open from Source Control and then selecting my solution folder. I did that and voila, my problem was fixed. I'm not sure why this was an issue, but I'd guess that it has something to do with source safe and my local project/solution files not syncing up correctly.

Access The Page Object From a Separate Class in ASP.NET

I just found this out today and thought you might want it for your notes. For awhile now I've been under the impression that the Page object was inaccessible from a class outside of the codebehind page. Today I found out that I was wrong. To access the Page object you simply need to do the following:


Page page = (Page)HttpContext.Current.Handler;

It would have been alot nicer if they included a property of HttpContext that would work like this: HttpContext.Current.Page, but for some stupid reason they did not do that. Anyway, I know that I was happy to find that out so I thought you might want to know too.

Dynamically Add and Modify Server Side Buttons in an ASP.NET Datagrid or Repeater

Within the last few weeks I was asked about how to dynamically format the output of either datagrids or repeater objects in .Net. My response at the time was to do something like the following:

<%# SomeMethod(DataBinder.Eval(DataItem.Container, "data_column").ToString()) %>

Where SomeMethod() is a server-side method in the code-behind which evaluates the bound-data given to it as a parameter, and returns it in the desired format. In some code that I've used this design, I send SomeMethod, which is called DateFormatter() in my code, two dates and then compare the two dates to see if they are the same. If they are the same then the method returns just the one date in MM/DD format, but if they are different then the method returns the two dates like so, MM/DD - MM/DD.

Recently I found that I had need for something a little bit more robust. I wanted to dynamically create server-side buttons in the repeater object which would have different parameters based on the data to which the item was bound. To do this I created a method and wired it to the OnItemDataBound Event of the Repeater. Here is basically what I did:

Aspx File

<asp:Repeater OnItemDataBound='ItemDataBoundMethod'>
...
<ItemTemplate>
<input type='hidden' id='parameter1' value='<%#DataItem.Container, "data_column").ToString())' runat="server" />
... Some More Hidden Fields ...
<!-- Below I create a button with no parameters and no events wired to it, so that I can set them dynamically in the OnItemDataBound Event-->
<asp:Button id='ButtonTemplate' runat='server' />
</ItemTemplate>
...
</asp:Repeater>


Code-Behind

protected void ItemDataBoundMethod(Object sender, RepeaterItemEventArgs e)
{
//Make sure that this is an item and not a header, footer, etc.
if(e.Item.ItemType == ListItemType.Item e.Item.ItemType == ListItemType.AlternatingItem)
{
//Here I access the hidden fields with the Id that I gave them
string param1 = ((HtmlInputHidden)e.Item.FindControl("parameter1")).Value.ToString().Trim();
...
Button buttonTemplate1 = ((Button).e.Item.FindControl("ButtonTemplate"));
...
...Evaluate the values in the hidden field and then set the properties of the button accordingly...
...
}
}

COM Objects are not destroyed when using Server.CreateObject in Classic ASP (Jscript)

Recently it was discovered that, on a particular ASP page in our application, COM objects that were being created using Server.CreateObject were not being properly destroyed. Actually, I should rephrase that, they weren't being destroyed at all until the component services package was shut down. If I kept task manager open, and watched the mem usage of dllhost.exe, I could see the memory being used by the processed increased each time I hit the page in question, and never decreased. Also, if I went to Administrative Tools -> Component Services and looked at the Objects, Activated, and In Call columns for the COM objects in my package, I could see those numbers ever increasing without decreasing.

The code is written in JScript and looked something like this:

var o = Server.CreateObject("dllname");

/* do stuff... */

o = null; //<--- I thought that this line should cause this object to be destroyed, I was //wrong

After much searching I came across this article: PRB: COM Objects Created in JScript Not Released Immediately. The article basically says that because JScript uses deferred garbage (for performance reasons), it can sometimes delay object destruction (in my case, permanently delay it). The article offers two solutions, the first suggest that a destructor be written for the dll, and the second suggests that the undocumented CollectGarbage() function be used. I chose the latter, simply because adding to the dll would be a bigger inconvenience (aka laziness).

A third suggestion that I would suggest, just based on conjecture from information provided in the article, is to rewrite the code block causing the problem in vbscript, as it is not effected by this problem (no garbage collection).

As for my problem, I'm not entirely sure why the problem occurs on only one page and not the others. My only guess is that on the page that is having a problem all of the COM objects are instantiated (about 14), whereas on most others only one or two objects are created.

The following is my code, after I fixed it:

var o = Server.CreateObject("dllname");

/* do stuff... */

o = null;

CollectGarbage(); // <-- This one little line fixed my problem

Opening Component Services In Windows Vista

I really don't hate microsoft, or Windows Vista, for that matter, but I do hate it when they move things around and don't tell you where they put them. To get to component services in Vista you should do the following.

1. Open "Run," I usually do this by typing "run" in the dynamic search embedded in the start menu.

2. Type "comexp.msc" in the run window, and then hit enter.

Alternative:
Go to "%windir%\system32\comexp.msc" and click it, or make a shortcut to it and put it in administrative tools so it can be with all it's XP buddies.

And that's that.

Cause a SQL Statement to Wait (Sleep, Pause)

At work I was trying to simulate a query that took a long time to execute, in order to debug a problem that I was tasked to fix. My goal was to write it in such a way that I knew exactly how long it was going to take every time. That's when I came across code that would allow to do exactly that:

WAITFOR DELAY '000:00:20';
SELECT column_list FROM table_name;

This code will wait for 20 seconds and then and then run the code that follows it, which is exactly what I was looking for.

Ruby On Rails, I'm not a fan

I hate ruby on rails.