----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: VinodK [TLabs, SCT] Message 10 in Discussion Hi all, Just my shot at this requirement. I donot see this as a problem as many should have come across such requirements. And I see that was a genuine good solutions given by all in this thread. I really excited ... :) ... Well, I see that harddisks are cheaper and never a matter of fact in this arguement. And the space requirements are not in the order of GBs for such a data structure. Given the fact that even a quick analysis on the staging table / junction table with 600 * 100 users ... I see the table (having UserID, PropertyID, PriorityID) max size of the table is around 750KB i.e less than a MB ... This is a small table yet for SQL Server ... :) ... If you have proper Indexes the query will still perform like a gem ... I propose a candidate key on all the userID, PropertyID column to be the Primary key. So that allows you to store the information of the same users together ... I bet that this will surely be fast enough even when the users get to few 1000's ... And now to the approach that NetPointer ... Thats good enough for the normalizing principles into account and to see the reusability approach. I would have liked if he coined the design to be template based ... By this whay we have done is that we are providing a set of templates predefined ... Now with 600 entries the combinations can be too much to be given as customization. And normalizing is a good aspect. But here I dont feel that convinced to this approach for this small table ... And as I saw this requirement, I thought I do something to just make it interesting ... :) ... This is just an interesting approach but NOT a solution I propose ... This came tomy mind if I can store a comma separated order list with each user and use that to show the output to a user ... Thought to share it with you all also ... :) ... Drop table tblProperty Drop table tblUser Go Create table tblUser (id int, name Varchar(30) , mySortOrder Varchar(7000)) GO Create table tblProperty (id int, propertyName Varchar(30)) GO Insert into tblUser(id,name) values (1, 'Vinod') Insert into tblUser(id,name) values (2, 'test') GO Insert into tblProperty values (1, 'color') Insert into tblProperty values (2, 'weight') Insert into tblProperty values (3, 'height') Insert into tblProperty values (4, 'Width') Insert into tblProperty values (5, 'Volume') GO Update tblUser Set mySortOrder = '2,4,3,1,5' Where id =1 GO Update tblUser Set mySortOrder = '3,2,1,5,4' Where id =2 GO Create View SomeThingInteresting AS Select Top 100 Percent derived.UserID, id , derived.propertyName, Right(derived.a,1) [Actual Value] from ( Select u.id [UserID], p.id, p.propertyName, 1000000 * charindex( ','+ Cast(p.id as varchar(4)) +',', ',' + mySortOrder + ',') + p.ID a From tblUser u, tblProperty p ) derived Order by derived.a, id GO Select * from SomeThingInteresting Where UserID = 1 Select * from SomeThingInteresting Where UserID = 2 This is a nice SQL Server trick I think ... Finally my verdict is to weigh the options that are suitable for your app and then use it ... There can be many more options that can come out ... HTH, Vinod Kumar Microsoft India Community Star www.ExtremeExperts.com ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/BDotNet/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
