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