Table design to maintain sort order

• kneekill

  SSC Journeyman

  Points: 96

  More actions

  November 17, 2010 at 10:12 pm

  #231719

  Hi All,

  I have scenario to design a table to save sort order for a combination made in Table1

  Table1

  ID

  --

  1

  2

  3

  4

  5

  6

  7

  the combination will be made between the IDs of Table1 like below

  1 - 4

  1 - 3

  1 - 7

  2 - 6

  2 - 5

  So the new table design i have in mind is

  MasterID---ChildID---SortNumber

  1----------4---------1

  1----------3---------2

  1----------7---------3

  2----------6---------1

  2----------5---------2

  But 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---ChildIDs

  1----------4,3,7

  2----------6,5

  every 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.

• vinothraj

  Ten Centuries

  Points: 1349

  More actions

  December 6, 2010 at 9:52 pm

  #1259358

  Sorry, its quite confusing me. Could you elaborate in detail?