I appreciate any comments on the design I'm implementing. Thanks to R937 for setting me on the path to types/subtypes

Here is where I'm at, at the moment.

I have a table of vehicles. Each vehicle can have a number of events associated with it (transportation, delivery, maintenance, failure, etc) The relationship between these is easy:

(PK and FK used for ease of writing, I don't actually use those names as my columns)
Vehicle.PK = Event.FK

All events have some simple information associated with them like a date and description. However, depending on the event type, there might be additional information specific to that type. So I've set up additional tables event_maintenance, event_delivery, etc.

I set up the tables such that:
Event.PK = Event_SUBTYPE.FK (for each subtype table)

The problem is that now I can have multiple subtype rows associated with one event, which doesn't make sense. I can set a one-to-one constraint, but I only know how to do that across one pair of table.

The other catch is that for one particular kind of event (delivery) one of the subtype rows could be associated with multiple events (i.e. multiple vehicles in the same delivery)

As I said, I appreciate any suggestions on how to set this up.

well, it actually does, although i think i can see the confusion

an event can have any event subtype, and in practice, your application logic would ensure that a subtype row is created only in the correct subtype table and none other (it wouldn't make sense for an event which is a delivery to have rows in both the delivery subtype table and the maintenance subtype table)

there are ways that you can enforce this in the database but they are a bit complex, and if you are okay with letting application logic do it, then i am too

as an aside, consider if you didn't have subtype tables at all, but had one master table with nullable fields for all the different subtype columns... do you normally have application logic to ensure that only the correct set of subtype columns and no others are populated by the application? because that's ~really~ hard to do in the database

anyhow, the other side of this question is retrieving data from subtype tables, and that's either done in two queries (get the event, find out what type it is, and query the appropriate subtype table) or one query with as many LEFT OUTER JOINs as there are subtypes

incidentally, what you get back from the single query with all the LEFT OUTER JOINs is a record set that looks exactly like the single master table with all the nullable columns

helps?

Thanks for your quick and helpful reply. That actually makes sense. In my drive toward a wonderful database design, I'd forgotten there would be a front end that could enforce certain logic rules. Seems like this might be the best approach for now.

These two links also had some useful ideas on how to implement what I wanted to accomplish:
Data Based : Distributed Keys and Disjoint Subtypes
and
Implementing Table Inheritance in SQL Server - SQLTeam.com

both of those are excellent articles, and both deal with what i mentioned earlier -- "there are ways that you can enforce this in the database but they are a bit complex"

if you understand what they are suggesting, then you've got a good handle on this particular data model