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 Sequencing Records 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 "Sequencing Records" Watch "Sequencing Records" New topic

Sequencing Records

ramnna jain
Ranch Hand

Joined: Mar 16, 2009
Posts: 129

I have three tables 'events', 'event_participants' & 'participant_work_submissions'.

Event table stores different events.
Event_participants table stores participants participating in different events.
Participant_work_submissions table stores work submitted by participants in different events.

Event - columns:
1. event_id
2. start_date
3. event_description

Event_participants - columns:
1. participation_id
2. event_id
3. user_id

Participant_work_submissions - columns:
1. submission_id
2. participation_id
3. submission_date
4. work_description
5. work_sequence_number

Here I want to assign a sequence number to the submitted work in any given event.



participation_id event_id user_id
1 1 1
2 1 3
3 2 9
4 2 5
5 2 1
6 1 7


submission_id participation_id submission_date work_sequence_number
1 1 'some date' 1
2 5 'some date' 1
3 2 'some date' 2
4 4 'some date' 2
5 3 'some date' 3
6 6 'some date' 3

What I want how to maintain the sequence number in column 'work_sequence_number'.
Means each event has works with their work submission sequence number.
ramnna jain
Ranch Hand

Joined: Mar 16, 2009
Posts: 129

I hope I explained my question properly, but even though if some one have any question, please ask, I will explain it.

Looking for response.

I am also trying to figure out the solution: Before I insert new record I can fire query to get the number of works submitted(count) to that event.
While inserting the record I will count+1 in the 'work_sequence_number' column.
But while doing this if some other user submitted his/her work then it would be difficult to resolve to whom I assign that number.

Please help..
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

I'm not sure what your work_sequence_number does. You already have a submission_date, doesn't this imply your sequence?

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
ramnna jain
Ranch Hand

Joined: Mar 16, 2009
Posts: 129

suppose in any given event there are 100 work submissions.
And the event controller wants to see them all.
In our application we show 10 recent works on one page & pagination.

Means the first work shown is 100th.

second work 99th.

third 98th ... and so on till 91th.

if any how i figure out this number while storing the records then while retrieving the same I can get the 'work_sequence_number' which is #100, #99 for any given event.

But I fear while assigning the 'work_sequence_number' if some more people submitting their works in the same event then!
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

So its just the ordering you need? Can submission date not be used? If it can't you could use a sequence (if your database supports it) to get values for your work_sequence_number column.

ramnna jain
Ranch Hand

Joined: Mar 16, 2009
Posts: 129

Thank you Paul.

I am using MySQL 5.1. I try to use the sequence, if it is supported or try it with submission date.
I agree. Here's the link:
subject: Sequencing Records
jQuery in Action, 3rd edition