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.
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.
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".
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 ]
Joined: Mar 25, 2001
I think the term you are looking for is "associative entity". Actually "composite entity" (or "bridge entity" or just "bridge").
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..
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.
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.