I need to implement a one-to-many relationship in an application and, when it comes to actually inserting the database records, I'm at a bit of a loss. In my case, I've got Triggers and Events. A Trigger is related to 0 or 1 Events. An Event, however, is related to 1 to n Triggers. For the first phase of the project, we're only going to allow 1-to-1 relationships to be entered, but I'd like to build the database in such a way that we don't have to restructure it for the next phase.
So, in order to keep this in a database, I imagine I need to have a structure that resembles this:
The architecture being used is one in which Java will invoke a stored procedure in SQL Server to perform the updates. In that stored procedure, I believe I need to follow these basic steps (remember that, for now, all relationships are one-to-one):
1. If an Event exists for a given trigger, insert the event. Allow SQL Server to generate a unique ID for the event. 2. Retrieve the unique ID for the event just added to the database. 3. Insert the Trigger using the Event ID just acquired, if an Event existed.
The approach that I have been using uses a timestamp on all Event records in order to retrieve the most recently added record. Specifically, every Event record has an UpdateTime column, which, by default, is populated with the current date and time when a new record is inserted. Then, I can use MAX(UpdateTime) in order to retrieve the ID for the most recently added event.
However, I've been seeing intermittent errors and I wonder if the problem is that multiple events are being added with the exact same Update Time and I'm then getting the wrong ID back.
If that's the case, how does one go about this? Unfortunately, there's nothing inherently unique about an Event - it consists of a Name, a Category, a Classification, and Comments (which are optional). There's nothing that says a single situation couldn't have two of the exact same event, caused by different triggers.
At risk of answering my own question, here's another thought...
As I'm using SQL Server and SQL Server stored procedures, can I create a new unique ID by using the newid() method, which generates a new GUID value? My ID fields could be uniqueidentifier fields and I could then stuff my new ID into those. Will that work and avoid any concurrency issues I might otherwise run into?