Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sequencing Records

 
ramnna jain
Ranch Hand
Posts: 129
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

e.g.


Event_participants

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



Participant_work_submissions

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
Posts: 129
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure what your work_sequence_number does. You already have a submission_date, doesn't this imply your sequence?
 
ramnna jain
Ranch Hand
Posts: 129
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 129
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic