Designing an "Events" Table with Multiple Table References in a Data Model

Question

Although the original problem was presented to me as an MSSQL database implementation, what I'm discussing here is a general perspective, not specific details.

Let's consider that I have three base tables:

1. Drivers

2. Vehicles

3. TransportCompanies

Each of these tables has its own primary key and a set of fields unique to them. For example, the Drivers table contains attributes like Name, Address, BirthDate, and more, while the Vehicles table includes attributes such as LicensePlate, VehicleType, Brand, Model, and so on.

Now, there's a new requirement to implement an Events table. The goal is to store all events in a single table (as an arbitrary prerequisite, I suppose), given that the events share many common attributes such as Type (Unavailable, On Hold, Accident, Priority Request), DateTime from/to, Flags to determine if the event has a monetary cost associated with it, the Company personnel responsible for tracking or resolving it, and more.

I see two primary options for designing this:

1. The Events table can have three nullable fields that serve as foreign keys pointing to each individual table (i.e., Drivers, Vehicles, and TransportCompanies), along with a separate field (let's call it EventType) that identifies to which of the three related tables a specific Event record applies. Validation can be enforced with a trigger to ensure that one of the three foreign keys has a not NULL value and that it matches the one specified in EventType.

2. Alternatively, they could unify the three different tables into one Entities table, which has a unique primary key, a field identifying the EntityType (Driver, Vehicle, Company, etc.), and all the fields from the current 3 tables (Some fields like Name could be unified, but most wont). The EntityType field would be a foreign key to an EntityType table, providing flexibility for potential future Types. In this case, the Events table is simplified, as it only needs the EntityID foreign key pointing to the Entities table.

There could be a third option as well: maintaining separate Event tables for each of the Drivers, Vehicles, and TransportCompanies.

Of course, I'm simplifying the scenario, and it's worth noting that this is a legacy database where the Driver, Vehicle, and Company tables already exist as separate entities.

To reiterate, I'm not seeking the best solution for this specific case but rather exploring the ideal way to model this if there were no constraints and the database could be designed from scratch.

Answer 1

That "events share many common attributes" is not a valid reason to store them in a single table. They are conceptually different entities and each relates to a different main entity. They should be modeled as separate tables, and allowed to evolve independently.

Also it's often most efficient to store such "weak entities" with a primary key of (DriverId,DriverEventId), which in a BTree will optimize storage and access of the events for a particular DriverId.