There must be a reasonable solution... just can't think it out.

[PhilM]

The following table is the result of a query, which is working fine.

eventID roundID competitionID classID
------- ------- ------------- -------
1 1 1 Mod
2 1 1 Pro
1 2 1 Mod
2 2 1 Pro
1 3 1 Mod
2 3 1 Pro
1 4 1 Mod
2 4 1 Pro
1 5 1 Mod
2 5 1 Pro

Ultimately, I will need to be able to assign event numbers to each
competitionID/roundID/eventID combination, and I don't know whether it
would be best to modify eventID to enter sequential numbers, or to add
another field to the table, (events are numbered from 1 to xx over whole
competition), or to generate event numbers on the fly. The latter would make
it difficult to back reference.

Event schedule is printed out and posted for competitors to see when they
compete.

Problems occur when, prior to competition, last minute alterations are
required (say 5min before first event), that may see deletion of an event
each round, addition of an event each round, or a reorganisation based on
class (classID) which would throw any sequential numbering out the window.
When modifications are made, the schedule is again printed out to keep
competitors up to speed.

Assuming then, that I alter eventID, how would I approach shifting numbers
around?

Ideas on how to approach solving this would be immensely appreciated.

[Virgil Green]

"PhilM" <ph***@nospam.com.am> wrote in message
news:40***********************@news.optusnet.com.a u...

The following table is the result of a query, which is working fine.

eventID roundID competitionID classID
------- ------- ------------- -------
1 1 1 Mod
2 1 1 Pro
1 2 1 Mod
2 2 1 Pro
1 3 1 Mod
2 3 1 Pro
1 4 1 Mod
2 4 1 Pro
1 5 1 Mod
2 5 1 Pro

Ultimately, I will need to be able to assign event numbers to each
competitionID/roundID/eventID combination, and I don't know whether it
would be best to modify eventID to enter sequential numbers, or to add
another field to the table, (events are numbered from 1 to xx over whole
competition), or to generate event numbers on the fly. The latter would make it difficult to back reference.

Event schedule is printed out and posted for competitors to see when they
compete.

Problems occur when, prior to competition, last minute alterations are
required (say 5min before first event), that may see deletion of an event
each round, addition of an event each round, or a reorganisation based on
class (classID) which would throw any sequential numbering out the window.
When modifications are made, the schedule is again printed out to keep
competitors up to speed.

Assuming then, that I alter eventID, how would I approach shifting numbers
around?

Ideas on how to approach solving this would be immensely appreciated.

Generally speaking... add another field. The scheduling of an event should
not affect it's identity.

More specifically, and without knowing more detail, I'd suggest that you
generate a schedule and store it separately in its own table(s). Each change
in schedules might or might not require generation of a new schedule. If you
have a need to refer to old schedules, then create schedules as needed but
only use the currently active schedule at any one time. You might need both
abilities: modify existing schedules directly or generate new schedules
based upon curent information.

- Virgil

[sma1king]

"PhilM" <ph***@nospam.com.am> wrote in message
news:40***********************@news.optusnet.com.a u...

The following table is the result of a query, which is working fine.

eventID roundID competitionID classID
------- ------- ------------- -------
1 1 1 Mod
2 1 1 Pro
1 2 1 Mod
2 2 1 Pro
1 3 1 Mod
2 3 1 Pro
1 4 1 Mod
2 4 1 Pro
1 5 1 Mod
2 5 1 Pro

Ultimately, I will need to be able to assign event numbers to each
competitionID/roundID/eventID combination, and I don't know whether it
would be best to modify eventID to enter sequential numbers, or to add
another field to the table, (events are numbered from 1 to xx over whole
competition), or to generate event numbers on the fly. The latter would make it difficult to back reference.

Event schedule is printed out and posted for competitors to see when they
compete.

Problems occur when, prior to competition, last minute alterations are
required (say 5min before first event), that may see deletion of an event
each round, addition of an event each round, or a reorganisation based on
class (classID) which would throw any sequential numbering out the window.
When modifications are made, the schedule is again printed out to keep
competitors up to speed.

Assuming then, that I alter eventID, how would I approach shifting numbers
around?

Ideas on how to approach solving this would be immensely appreciated.

In addition to Virgil's comments, I would say to not get hung up on
renumbering the competitions every time there is a change (read deletion).
If you think people will be confused by the absence of competition 12 when
it's been cancelled, show it on the schedule as "Cancelled".

George