On Sun, Mar 17, 2002 at 12:28:35AM -0500, Lionlike wrote: > Hi, I need a little help as I am a beginner with db things. I'm > building a database for technicians and "help-desk" problems where > technical problem tickets are stored in one table, and technicians in > another. Really I have two main questions. First, each problem may > have more than one technician assigned. In the problems table were I > have a technician column, should I build an array that holds all the > primary keys for each technician assigned or should I build a third > table that links multiple technicians to a single problem? And my > second question is this, I select the technicians based on alphabetical > order out of a list. Each time there is an occurrance of a problem,I go > to the next tech in alpha order. How can I do this with MySQL? Or > would I have to organize in alpha order using PHP and select my next > record? I'd like to go with whatever doesn't absorb system resources > too badly and it just seems like having 3 to 5 users accessing this > system and having PHP alpha order the database over and over would do > that for each occurrance. And how could I "flag" a record so that I > would know where to get the next record once I have it in alpha order? > Thanks, > > David
Second question first: Create a little table that stores the ID for the last tech assigned a ticket. The next time a ticket is assigned, have your code retrieve the ID and search the tech table for the next ID. Your SELECT query can do any alpha ordering more efficiently than PHP. First question: Your tables are in a many-to-many relationship. Any tech is assigned one or more tickets, and any ticket is assigned to one or more techs. Use a join table to create the many-to-many relationship. The join table has only two columns: a tech ID column and a ticket ID column. Each time a new ticket is assigned, add one row to the join table for each tech assigned the ticket. Each row contains the tech ID and the ticket ID. This makes your SELECT queries a little more complicated, but in the long run it creates fewer problems than any other solution. Bob Hall --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
