• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Dealing with One-to-Many Relationships

 
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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: 40198
816
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • 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: 8943
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
  • 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
 
I think he's gonna try to grab my monkey. Do we have a monkey outfit for this tiny ad?
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic