| Author |
Topic |
|
kneekill
Yak Posting Veteran
76 Posts |
 Posted - 2010-11-17 : 10:20:12
|
| Hi All,I have scenario to design a table to save sort order for a combination made in Table1Table1ID--1234567the combination will be made between the IDs of Table1 like below1 - 41 - 31 - 72 - 62 - 5 So the new table design i have in mind isMasterID---ChildID---SortNumber1----------4---------11----------3---------21----------7---------32----------6---------12----------5---------2But the problem is the ChildID sort numbers keeps changing many times and i need to update/delete/insert multiple rows with the new sort numbers for each masterID.Also i cannot delete the whole combination for a masterID and reinsert new sort numbers, as in future i need extend the sort table beyond the purpose of saving sort numbers.second table design i have in mind is to save childIDs comma Separated and build sort number dynamically at run time.MasterID---ChildIDs1----------4,3,72----------6,5every time there is a change in childid combination i will just update the childids Separated by comma. But this is not good solution due to data integrity and no referential integrity.I request you to guide me to a better table design for the scenario.If any one of you faced a similar scenario, please let me know the table design for your solution. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-11-17 : 10:40:48
|
| Why not just have MasterID, ChildID and use ROW_NUMBER() OVER (PARTITION BY MasterID ORDER BY MasterID, ChildID ASC) AS SortNumber in your queries? The overhead is small and no need to worry about when children are added to your result set.This is assuming you are using SQL 2005 or above. |
 |
|
|
kneekill
Yak Posting Veteran
76 Posts |
Posted - 2010-11-17 : 11:34:50
|
Hi RickDThanks for replying. How will Row_Number() help if only the sort numbers related to childIDs are updated/modified ?. To clarify more sort number in the table is saved/used for some custom functionality in the front end (Custom Sort).quote:
Originally posted by RickD
Why not just have MasterID, ChildID and use ROW_NUMBER() OVER (PARTITION BY MasterID ORDER BY MasterID, ChildID ASC) AS SortNumber in your queries? The overhead is small and no need to worry about when children are added to your result set.This is assuming you are using SQL 2005 or above.
|
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-11-17 : 11:53:19
|
| What I am saying is don't save this value to your table, use it in the query on the front end instead. You shouldn't be saving things like this in a table anyway. |
|
|
|
kneekill
Yak Posting Veteran
76 Posts |
Posted - 2010-11-17 : 12:16:30
|
Hello RickD, then how can one preserve a custom sort (between different user sessions or states)? if not in the database.can anyone else please suggest me a design for scenario like thisquote:
Originally posted by RickD
What I am saying is don't save this value to your table, use it in the query on the front end instead. You shouldn't be saving things like this in a table anyway.
|
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-11-18 : 04:26:03
|
| Ok, you didn't mention that this was between sessions. How can it be between sessions if you then want to add things to it the next time you run the query for a particular user?You either give the user the same results and go with your first idea, or you redo the sort order each time the user logs on and just get them to sort by a certain field rather than a row sort, this way you can just save the field id (which will also help with size of table [depending on No of users]). This is probably a better idea if you are going to be using this table to save other information. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-11-18 : 09:00:11
|
| can you explain how sort order will vary between session? whats the basis of this change?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
kneekill
Yak Posting Veteran
76 Posts |
Posted - 2010-11-18 : 13:06:28
|
hi visakh16, i mean to sort order needs to be preserved between the sessions.example:user 1 makes some sort order, the same sort should be available for user 2 and vice versaquote:
Originally posted by visakh16
can you explain how sort order will vary between session? whats the basis of this change?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
kneekill
Yak Posting Veteran
76 Posts |
Posted - 2010-11-18 : 13:07:00
|
quote:
Originally posted by kneekill
hi visakh16, i mean to say sort order needs to be preserved between the sessions.example:user 1 makes some sort order, the same sort should be available for user 2 and vice versa and so onquote:
Originally posted by visakh16
can you explain how sort order will vary between session? whats the basis of this change?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-11-19 : 10:46:14
|
| so when will sort order change? will it be done manually done by somebody?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
kneekill
Yak Posting Veteran
76 Posts |
Posted - 2010-11-19 : 16:43:08
|
Yes exactly, it will be manually done by click of a button in the UIquote:
Originally posted by visakh16
so when will sort order change? will it be done manually done by somebody?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
|
Table design to save sort order