I’m in a quandry. I am building a reserved number system that will let a manager keep track of which competitor is entitled to a favorite number. These organizations run different types of events so this number manager must provide some flexibility. A schema with example data follows:
Participant | Event Type | Bicycle | Class | Grid | # |
---|---|---|---|---|---|
Brian | 12 | ||||
Joe | X-Country | 12 | |||
Brian | X-Country | Klein | Pro | Red | 12 |
Heidi | X-Country | Specialized | Pro | Blue | 12 |
Mary | 1002 | ||||
Frank | Downhill | 777 |
What IS OK:
- Because this is an ASP, each organization may run more than one type of event (e.g., Downhill, Cross-country, etc.) and the reserved number may differ by event type (based on first come-first served)
- Each participant may have more than one bike which can each have a reserved number specific to it
- The numbers can be specific to a particular class and/or grid. There can be both a #12 in Pro and Novice in the same race type (like Brian and Heidi) and that’s OK.
The more specific entry would win out in case of a conflict: If Joe registered for the X-Country race in Pro/Blue and tried to use #12, Heidi would have first dibs on it for that race.
What is NOT OK is:
- A number conflict with the same level of specificity. E.g., with the same values for event type, class, grid and number. Any of these can be blank except the number.
- Duplicate entries (e.g., I don’t want to allow Heidi’s entry to be inserted twice into the database (which also takes into account the NULLable “Bicycle” field)
The problem with this is that with allowing NULLs I have allowed duplicate entries. A primary key won’t work because it requires non-null data (this is Postgres for the record).
I have been experimenting with a series of indexes using predicates like:
CREATE UNIQUE INDEX idx_number ON numbers (member, bicycle, eventtype, class, grid, number) WHERE class IS NULL
CREATE UNIQUE INDEX idx_number2 ON numbers (member, bicycle, eventtype, class, grid, number) WHERE class IS NULL AND grid IS NULL
CREATE UNIQUE INDEX idx_number3 ON numbers (member, bicycle, eventtype, class, grid, number) WHERE class IS NULL AND grid IS NULL AND eventtype IS NULL
These are permitted by Postgres. But because up to 4 fields can be NULL, it seems like I would need 4! permutations, or 24 different indexes to cover the spread. This seems like a performance nightmare.
So I appeal to you, good reader, that you might have a more clever idea than mine or some experience in an analagous situation that would keep me from pulling out the few remaining hairs on my head.
brian said:
on June 23, 2007 at 12:43 pm
Well, I don’t know how well this is going to work, but for better or worse, I wound up using 15 indices to enforce my constraints over 4 nullable fields using IS NULL predicate. We’ll see how this goes…