Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

storing sequence array in DB

 
shikhar singh
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
greetings!
I have a business problem where I need to store a list of values in a sequence associated with a key, in db. Let me explain in detail.
In my application, there is are different workflows defined in a company. Any proposal entered by anyone follows one of the workflow which is basically the sequence of the Approvers.
Proposal is accepted when all the approvers in sequence approves it. Whole of the proposal lifetime should follow the sequence of the approvers.
Now, in my database design, I have a table where I will be storing workflowID(primarykey to identify a workflow) and approvers(approverIDs) related to the workflow. But there should be a sequence for these approvers.
following are the options I looked into:
If there is a way in sql to store array, my job will be easy, because I just need to add the approvers in the array with proper sequence and store it in db as single record. But there is no datatype which can support this.Can blob do this?? If yes how do i retrive the value?
Other way is to store the user in a sequence as comma delimited string as one record but disadvantage is parsing and creating the string for every read/write.
One more way is to maintain a seperate column for sequence number. But the biggest problem in this approach is when the user modifies this sequence drastically and in that case i need to take care of sequence number.
I will be thankful, if somebody could advice me on this.
Shikhar
 
Wagner Danda
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
why don�t you try to serialize the array and store it to the database... Maybe this works...
 
shikhar singh
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Wanda,
Thanks a lot for help.
I was thinking about this approach but don't you think this way we are bounding the data stored with Java program. Any client who is accessing this data has to deserialize it and should know the class it belongs to.
But I can live with this....
Is there any other alternate approach to this?
Thanks
Shikhar
 
shikhar singh
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I am actuall using data model db design and
the content of the array is linked to the other table. For example the array contains the list of userIDs which is further linked to the USER table. If I persist this java object as Blob in DB, it may lead to data inconsistency. Because somebody (admin) may delete the user and it may still be there in the blob object, unaware of the inconsistent state.
Is there any other way I can solve this problem.
Thanks in advance
Shikhar
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic