File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Dealing with One-to-Many Relationships Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Dealing with One-to-Many Relationships" Watch "Dealing with One-to-Many Relationships" New topic
Author

Dealing with One-to-Many Relationships

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
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


SCJP Tipline, etc.
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
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

Joined: Dec 20, 2001
Posts: 3271
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30382
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

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


Groovy
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Dealing with One-to-Many Relationships