File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Dealing with One-to-Many Relationships

 
Corey McGlone
Ranch Hand
Posts: 3271
  • 0
  • 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
  • 0
  • 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
  • 0
  • 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?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33671
316
Eclipse IDE Java VI Editor
  • 0
  • 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.
 
Pradeep bhatt
Ranch Hand
Posts: 8927
Firefox Browser Java Spring
  • 0
  • 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
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic