Orange is my favorite color

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.

1 Comment

  1. 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…

{ RSS feed for comments on this post}