aspose file tools*
The moose likes JDBC and the fly likes Query Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query Question" Watch "Query Question" New topic
Author

Query Question

Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

This is probably more of an SQL question than a JDBC question, but I'll ask it anyway.
I am updating an application I wrote where I assign trouble tickets to techs. I am updating it to assign multiple techs to 1 ticket and then any 1 of those techs will eventually accept the ticket.
My problem is I can't figure out a good way to store this. Originially I had an assignto_table and I stored the TicketNumber and a Tech Name. So there may be 3 entries of the same TicketNumber but 3 different tech names. The problem with this is when I chose to view trouble tickets that were not assigned to Tech A, my result set was returned with the other 2 entries, both having the same TicketNumber, so when I fill a table, there are 2 instances of the same ticket. Anyway...
I am needing to find some sort of good solution for this if anyone has any suggestions.
Thanks.


GenRocket - Experts at Building Test Data
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

Hi Gregg,
Sounds to me like you have two database tables that have a many-to-many relationship. A ticket can be associated with many techs, and a given tech can be associated with many tickets.
As you have noticed, many-to-many relationships between database tables can be difficult to manage. The usual way to resolve these difficulties is to introduce a third table (whose name in the standard database terminology escapes me now) that has a one-to-many relation with both the "ticket" table and the "tech" table. Thus it links a single ticket to a single tech. So where you have three techs associated with the one ticket (according to your example), this would be represented by three, distinct rows in the "translation" (for want of a better name) table.
Hope this has helped you.
Good Luck,
Avi.
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
I'd call that third table "Ticket_Tech" or "Tech_Ticket".
It should have only two columns, a tech ID and a ticket ID. The primary key is composite, consisting of both columns. The tech ID column is a foreign key referencing the ID field of the Tech table, and the ticket ID column is a foreign key referencing the ID field of the Ticket table.
I think the term you are looking for is "associative entity".


Ron Newman - SCJP 1.2 (100%, 7 August 2002)
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
GB: I am updating an application I wrote where I assign trouble tickets to techs. I am updating it to assign multiple techs to 1 ticket and then any 1 of those techs will eventually accept the ticket.
How is the difference between being "assigned" a ticket and "accepting" a ticket reflected in your data? Existence/nonexistence of a row on a table? A flag?
GB: My problem is I can't figure out a good way to store this. Originially I had an assignto_table and I stored the TicketNumber and a Tech Name. So there may be 3 entries of the same TicketNumber but 3 different tech names.
So a given ticket can be assigned to multiple techs. And presumably a given tech can have multiple tickets. Sounds like a classic "many-to-many" relationship. The classic way to handle this is to break the many-to-many relationship between tickets and techs into two one-to-many relationships: a single tech can have multiple assignments and a single ticket can have multiple assignments. The primary key of the assignment table is the combination of the techNumber and ticketNumber fields.

I'm using the assignment table for both "assigned" and "accepted." If a given assignment has only one associated tech, the assumption is that it's been "accepted." If a given assignment has multiple techs, they're all "assigned." When one "accepts," it's up to the application to delete any other rows.
In the data I've created, the only ticket that's been "accepted" is ticket AAC for Gregg. The other three tickets have been "assigned" to multiple techs.
So I'm using the existence/nonexistence of rows of data on assignment to indicate whether a ticket has been assigned or accepted. There are other ways of handling this. This just happends to be what I picked.
GB: So there may be 3 entries of the same TicketNumber but 3 different tech names. The problem with this is when I chose to view trouble tickets that were not assigned to Tech A, my result set was returned with the other 2 entries, both having the same TicketNumber, so when I fill a table, there are 2 instances of the same ticket.
Here's where you lose me. So is the problem that your query returns 2 entries when you're expecting 1 or that it returns 2 entries when you're expecting zero?
"Not assigned to Tech A" suggests the following to me:

Miranda has these tickets:

But these tickets are "not assigned to Miranda":

Are these the kinds of results you're trying to get? If not, using the data I've provided, show me the kind of result set you want to produce. (Or if my data doesn't work, provide some data to use.) (Or if my table stuctures aren't what you're after, show me ones you like better.)
GB: tickets that were...
"Were" implies history to me. Are you trying to keep a history of changes? If so, that adds a bit of complexity to the issue.
[ July 21, 2003: Message edited by: Michael Matola ]
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
I think the term you are looking for is "associative entity".
Actually "composite entity" (or "bridge entity" or just "bridge").
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Thanks for all the responses. I have solved the problem "temporaily" to get passed this and move on.
However, I need time to look over Michael's response so that I understand it a bit better. Just didn't want you all to think I was not reading these..
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Michael. Your post was a big help. But I am still having a problem. The query where you got the following data:
Mike AAA It won't print
Gregg AAA It won't print
Luz-Marie AAA It won't print
Gregg AAC Corrupts data
The problem is I only need the Ticket Number and I only need the ticket number once, so when I say, "Give me all ticket NOT assigned to ME" The only output I should get is:
AAA
AAC
and I haven't found a way of doing this just in SQL.
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
Then you want to say
select distinct ...
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Thanks Ron. Got it working. Thanks to everyone.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
Yeah, what Ron said.
GB: The problem is I only need the Ticket Number...
Now you telll us...
Anyhow, the thing to keep in mind when using the "distinct" keyword is that how it affects the results depends on what items you have in your select list. For example, if you have select distinct field1 from..., your results will contain unique values of field1. If you have select distinct field1 , field2 from..., your results will contain unique field1-field2 combinations. Etc.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Query Question