This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of DevSecOps Adventures: A Game-Changing Approach with Chocolate, LEGO, and Coaching Games and have Dana Pylayeva on-line!
See this thread for details.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Devaka Cooray
  • Tim Cooke
Sheriffs:
  • Rob Spoor
  • Liutauras Vilda
  • paul wheaton
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Mikalai Zaikin
  • Carey Brown
  • Piet Souris
Bartenders:
  • Stephan van Hulst

Dealing with One-to-Many Relationships

 
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,

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.

I hope some of that made sense. So, any ideas?

Thanks,
Corey
 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok, here's a thought...

What if I control the ID's of my records, myself? I mean, what if I follow these steps, instead:

-- If an Event Exists
1. SELECT MAX(EventID) from Event table
2. Add 1 to MAX(EventID)
3. INSERT Event into Event table using new MAX value

-- Always
1. Insert Trigger into Trigger table
1a. Use the Event ID just created, if an event exists

Will this work? Will I have to worry about multiple people updating at once and crashing into each other because they end up with the same ID's, which will be used as primary keys?
 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
author & internet detective
Posts: 41988
911
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Corey McGlone:
Will that work and avoid any concurrency issues I might otherwise run into?


Yes. It will also be faster than managing it yourself through transactions.

In Oracle, you would use a sequence to do this. That allows you to reuse numbers across tables making for smaller keys. I'm not sure if SQL Server supports sequences though.
 
Ranch Hand
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

In Oracle, you would use a sequence to do this. That allows you to reuse numbers across tables making for smaller keys. I'm not sure if SQL Server supports sequences though.



MS SQL servers supports identity columns. Use that.

http://www.databasejournal.com/features/mssql/article.php/3307541
 
You may have just won ten million dollars! Or, maybe a tiny ad.
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic