File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes storing sequence array in DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "storing sequence array in DB" Watch "storing sequence array in DB" New topic

storing sequence array in DB

shikhar singh
Ranch Hand

Joined: Oct 02, 2000
Posts: 31
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.
Wagner Danda

Joined: Jul 10, 2001
Posts: 26
why don�t you try to serialize the array and store it to the database... Maybe this works...

Wagner Danda<br />Sun Certified Programmer for Java 2 Platform
shikhar singh

Joined: Jan 25, 2001
Posts: 11
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?
shikhar singh

Joined: Jan 25, 2001
Posts: 11
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
I agree. Here's the link:
subject: storing sequence array in DB
It's not a secret anymore!