Sunday, April 10, 2011

OSDNNSKINS: New version of cash and multiflex with portal template available

Hi all,

New Version for Cash and Multiflex is available for download. This time we have three common changes for both of the skin packages.
  1. There is only single package for skin and container installation
  2. Portal template is available to quickly evaluate the skin. 
  3. Both of the skins are tested in dnn 6.0.0.44 
For those who are not knowing about this, open source dotnetnuke skins is an initiative to provide high quality free skins to dnn community.

More contributors to osdnnskins:

We have more contributors to join the party, so we welcome issues and suggestions from community in order to help them provide quality skins for free.

there are lots of evaluations and  also there are many sites using skins from osdnnskins, there are very less number of comments and feedback that we get.


Please please please provide your feedback about any skin that you evaluate and you can directly go to osdnnskins.codeplex.com to create a new issue or you can email me any time for any kind of help regading the same.
You can contact email @ lakhlaniprashant [at} gmail [dot} com

Thursday, April 7, 2011

Database Design : Smartly storing many user settings with Boolean values in a single value

It is obvious that when we design database tables for storing user preferences we look user settings as one-to-many relationship, but there are cases when all your setting values are boolean. For example, you have many type of newsletters (infoq.com has .net, java, soa etc) and you want to user choices, OR you have many categories of articles and you want to store which categories user is interested to browse, user choose tags (fev tags)  are also good example in stackoverflow.com.

Problem:
How to efficiently store user preferences which are all having boolean values.

Classic solution:
  • Create a subscription_type database which stores all the subscription types
  • Create a user_subscription table user_subscriptions with columns user_id and subscription_id
  • Insert a new row for each kind of subscription for each user
  • Delete a row for each kind of subscription user un-check (In case user checks the subscription and un-checks it after some days)
This will work fine but the only problem is to maintain an extra table for storing the details.

Better Solution:
  • Create a subscription_type table which stores all the subscription types.
  • add a new column in users table for subscription.
  • use following query to retried user subscriptions:
    select
            user_id,
            case when
                subscription & power(2,subscription_type_id) =0
            then 0
            else 1 end as is_subscribed,
            subscription_type_id,
            subscription_type_name
        from users cross join subscription_types
        where user_id = @user_id
  • user following query to store user subscription:
    select sum( power(2,subscription_type_id)) from subscription_types where subscription_type_id in (1,2,3)
here subscription_type_id in (1,2,3) describes the subscription type id user is subscribed to.

How It Works:
We are storing sum of binary power as user preference. For example, user is subscribed to subscription type 0,1, and 3 we are storing power(2,0) + power(2,1) + power(2,3) = 1 + 2 + 8 = 11.
For reading a boolean value of subscription we are using t-sql’s AND (&) operator. We are doing and operation of 11 and binary power of subscription type, and it we get 0 means user is not subscribed, otherwise we will get power(2,subscription_type) as result of AND operation.

Conclusion:
We can say the solution is better because you can using single query to read and write the subscription. It is more faster, and requires no additional table. There are many other advantages that you can think of.

Limitations:
Every time you take this kind of solution, you are killing possibility to store details that depends on the relationships. In our subscription example, you will never get information about when does a user subscribed to which subscription type individually.
If that is not important to you, you can obviously go for it.

Note:
Please note that in actually situations, subscription table and related designs are really more complicated than what I’ve shown above, so it demonstrates the example scenario and not the example database design for any kind of system.
Have fun with your database design!

Popular Posts