• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

storing sequence array in DB

 
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
why don�t you try to serialize the array and store it to the database... Maybe this works...
 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Proudly marching to the beat of a different kettle of fish... while reading this tiny ad
New web page for Paul's Rocket Mass Heaters movies
https://coderanch.com/t/785239/web-page-Paul-Rocket-Mass
reply
    Bookmark Topic Watch Topic
  • New Topic