Re: data model help, was Re: Using null date fields to indicate active/expired records

Brian,

> Since it is intelligible and provides useful information at the
> application development and debugging level, I don't consider it a relic.

It's your app. Just let me know who the client is in case they ever call me.
I charge extra to deal with HU ;-)

> 1. Using NULL dates versus a boolean indicator, for example, in the
> tblMapAttendeeFlags. The extra information about start and end is not
> really necessary but may be a "nice to have". My original inquiry
> regarding performance, if it was determined the date approach was
> considerably slower, would have swung me to use boolean here.
> Membership type, however, in tblMapMemberType requires the tenure of a
> position so that will use the date approach, regardless of performance.

Can you describe the purpose of each field in this table? I think that's far
more crucial than any performance criteria.

> 2. I'm not sure about feeding the PK of tblMapAttendeeClasses from to
> tblMapEventClasses. The image I sent doesn't have it this way but this
> requirement would force the attendee classes to use those classes that
> have been "offered" explicitly. I can't decide if tblMapEventClasses is
> completely unnecessary or should be more important?

I'm afraid that I don't understand your application well enough to answer that
sort of question. Particularly, you appear to be doing a fair amount of
object-relational mapping here and that always requires a thorough
understanding of the application which I don't have.

> 3. tblMapAttendeeClasses stores 1-n records of a tblLookupAttendee
> record to specify which event parts the attendee participates in (along
> with class, vehicle, instructor, grid pos, etc). I've currently not
> referenced tblLookupAttendee (uidClubMember, uidEvent) but I'm wondering
> if I'm better off doing so?

See above.

> 4. Does my approach for storing arbitrary custom questions (that may be
> of several data types) in tblLookupCustomQ and tblMapCustomQ seem OK?
> I'm sure others have come across a similar requirement; thoughts?

Define "question".

--
Josh Berkus
Aglio Database Solutions
San Francisco