June 17, 2011 at 2:10 am
Hi, Im new here so hello all 🙂
Ive had a look through the forums and cant see a post that actually answer my question - if there is please point me in the right direction.
I have a table, for helpdesk ticket information.
As the helpdesk can be used by different departments there is a primary key of:
"TicketNo,DeptCode"
I.e. our helpdesk tickets (concatenated as DeptCodeTicketNo" could be MIS001, MIS002, FIN001, FIN002......
I want to use the IDENTITY option on SQL to update my number however, this also needs to be based on the department code.
Is this possible using IDENTITY or do i need to manage it some other way.
I.e. if i just use the basic identity i would end up with numbers MIS001, MIS002, FIN003, FIN004
I look forward to hearing from you
IAN
June 17, 2011 at 3:48 am
Nope. Identity is a number field. It would need to go into a number data type such as int or bigint. What you could do is create a calculated field that combines the generation of the identity column and your department. But even that is going to be a problem for you because it won't number MIS001, FIN001, FIN002, MIS002, but will go MIS001, FIN002, FIN003, MIS004. If you really want the former type of numbering scheme, you need to create it manually. I wouldn't recommend that approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 17, 2011 at 4:13 am
Hi Grant
Thanks for the feedback, I kind of guessed the manual approach was going to be the right route.
Thanks
Ian
June 18, 2011 at 9:00 am
Just a thought. I definitely wouldn't do things this way.
First, because of the 3 digits you allow, you're limiting each department to only 999 tickets. Further, using a cyclic counter for each year (or month) to overcome that problem will prove to be a huge and certain pain.
Second and as you're already finding out, maintaining a separate counter for each department is already proving to be a pain for you. To do separate numbering for each department, you either need a separate table for each department (really bad idea programatically speaking), you need to maintain a "Sequence" table (which is another really bad idea programatically speaking), or you need to calculate the next ID number from existing data (which is another really bad idea programatically speaking).
Third, storing a column with two types of information (Department code and sequence #) in it is a violation of normalization and you'll eventually find that to be a huge pain, as well. The reason why people want separate sequence numbers is because they think that's a cool way to quickly identify how many tickets a department has used, that it looks "pretty", or some other almost personal reason and none of them are the right reasons because none of them make sense programatically or storage-wise. It doesn't even make good sense spreadsheet-wise.
Now, it's your data and the people having you do this have the best intentions, I'm sure. But, if it were me, I'd tell everyone that I wouldn't let them make this huge design error. Instead, I'd have one column for the Department code and an IDENTITY column as a ticket number. That will easily allow for virtually any kind of reporting you want/need but, more importantly, that will make your computational life so much easier because you won't have to figure out why one of the other methods ended up producing a duplicate ticket number. To make display of a ticket number (what you seem to want is a display value and should never be confused with what needs to be stored) easier, you would also create a persisted calculated column to concatenate the Department Code and Ticket Number (the IDENTITY column).
Of course, that's just my opinion on how to do this with the least pain both now and in the future. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2011 at 10:14 am
I tend to agree with Jeff, but you can add a TicketDate with a datetime value, and if needed, you can display a combination of your Year, Department ID, and Identity value as the ticket.
As Jeff mentioned, don't let the requirements of what people want to see affect good design. You can show them anything, and rearrange the data in the application, but design and store it appropriately.
June 20, 2011 at 1:14 am
Identity values will skip for rolled back transactions so it wouldn't be a good indicator of number of tickets per dept anyway. Unless your expecting a huge number tickets raised every second , it might be worth considering getting max ticket number and incrementing the number by 1.
I assume the tickets should have some inherent meaning so that they can be routed to the right dept just by looking at the format(either manually or automated) , but if this is not the case its better to use your own internal numbering scheme and display a formatted version at the application level.
June 20, 2011 at 1:39 am
Hi Jeff
Thanks for this, and yes I agree its not the best way to be doing things.
The idea really behind it is that the helpdesk system being incorporated could be used by other departmetns as their own helpdesk system to, so MIS could have their own system, the design team could have their own system (for their own issues internally) and the idea was that we could all share the same DB etc.
However, I guess (and as was my original proposition) that there should be seperate implementations of helpdesk for those who need it, so MIS have one the business can use and design have one they use for themselves. Its not a massive DB so should not be an issue.
Many thanks for your feedback, its very much appreciated.
June 20, 2011 at 5:22 am
iancadwallader (6/20/2011)
Hi JeffThanks for this, and yes I agree its not the best way to be doing things.
The idea really behind it is that the helpdesk system being incorporated could be used by other departmetns as their own helpdesk system to, so MIS could have their own system, the design team could have their own system (for their own issues internally) and the idea was that we could all share the same DB etc.
However, I guess (and as was my original proposition) that there should be seperate implementations of helpdesk for those who need it, so MIS have one the business can use and design have one they use for themselves. Its not a massive DB so should not be an issue.
Many thanks for your feedback, its very much appreciated.
But you can use that approach, just don't build the key values they way you're doing, or at least don't store it that way.
What about simply using an identity on the table, and add a lookup value for department to another table, where you can add business units or even change their names without ever affecting the key value on that table, another identity. Then, to display information to the users, you can use the values from both and show them MIS00001 or whatever.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2011 at 5:42 am
Thats the route Ive gone down now, im just having problems at the minute trying to retrieve the data... im still pretty new to VB/SQL (Ive been a Progress 4GL developer for 20 years !).
Im having difficulty trying to send a string to my select statement even though i encapsulate it in the ' and ' (i.e. "where field = ' " & myvar & "' " .. it seems to keep seeing blank.
But thats a different story!
Many thanks for you help, its been very much appreciated.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply