Combining Characters with Id Field

Question

I'll create an Issue table in an MVC5 application and I want to use special code for each type of the issues as below:

For IT related questions INF-0001, INF-0002, ... For General type of questions GEN-0001, GEN-0002, ...

As I use all the issues on the same table, I think it is better to store the ID numbers as INF-0001, GEN-0001, ... etc. In that case should I use string as the data type of ID column in MSSQL? Or what is the best approach in order to store Id's with their related codes? I also think of using GUID, but I am not sure if it is possible. Thanks in advance.

You should absolutely have and ID (integer) field as your PK, for lots of reasons. This *issue code* could become and index too, but do no threat it as the PK. Also, be ready to come up with a solution to get the next sequence based on the category. — Andre Calil, May 05 '15 at 13:12
Thanks for reply. By keeping your suggestions in mind, I will use int for id fields instead of varchar. I voted up :) — Jack, May 05 '15 at 13:32
The last problem regarding to this issue is that: how can I create id numbers in order i.e. 1, 2, 3, 4 without any gap? I think it is not good idea to retrieve the max id just before creating a new item? Any idea? Thanks in advance again... — Jack, May 06 '15 at 06:52

teo van kot · Answer 1 · 2015-05-05T13:17:03.400

3

I suppose it's better create separate field for your custom names. So your table will have int Id (Primary Key) field and CustomName varchar(100) or nvarchar(100) type (If you use unicode characters) field with your custom names.

It will be better for perfomance to use int as Id if you will JOIN your file table with others. If you want to search values in this field and it is slow just create INDEX.

edited May 05 '15 at 13:17

answered May 05 '15 at 12:59

teo van kot

12,350
10
38
70

Thanks for reply. I think it is really logical to use int for id fields instead of varchar. I voted up :) – Jack May 05 '15 at 13:31

score 2 · Accepted Answer · edited May 06 '15 at 20:14

2

You could have a general issue id and a category, for example:

Table: Issue

------------------------------------
IssueID | CategoryID | CategoryIndex
------------------------------------
   1    |     1      |      1
   2    |     1      |      2
   3    |     2      |      1
   4    |     1      |      3

Table: Category

-----------------------------
CategoryID | Prefix | Name
-----------------------------
     1     |   INF  | IT 
     2     |   GEN  | General

Then you calculate the issue number when querying these tables. You can store the calculated number in a table if you want to keep track of the issue number in case of a change in the database (ex: the prefix for IT related questions changes from INF to IT)

Now that you have a good schema, how do you keep control of the category sequence on the issues table? Check this out:

DECLARE @categoryID INT
DECLARE @nextSequence INT

SET @categoryID = 1 --You'll have to change this!

SELECT @nextSequence = i.CategoryIndex
FROM Issue i
WHERE i.CategoryID = @categoryID

SELECT COALESCE(@nextSequence, 0) + 1 as 'NextSequence'

You can turn that into a stored procedure (NextSequence, maybe?) that receives an INT as parameter (the category ID) and returns another INT as result (the CategoryIndex for the new issue).

Finally, to create your full code:

SELECT
    i.IssueID
    , c.Prefix + '-' + RIGHT('0000' + CONVERT(VARCHAR(4), i.CategoryIndex), 4) as 'IssueCode'
FROM Issue i
INNER JOIN Category c ON i.CategoryID = c.CategoryID

edited May 06 '15 at 20:14

Andre Calil

7,652
34
41

answered May 05 '15 at 12:58

Sébastien Sevrin

5,267
2
22
39

A) It seems to be good. I think IssueID will be the PK and CategoryID will be FK and all the issue numbers will be given in order according to their CategoryIndex. Is that true? – Jack May 05 '15 at 13:29
B) On the other hand, there are 2 points about which I need to be clarified: 1) Should I combine the Prefix and CategoryIndex in order to show the issue on any page i.e. IT-0001, GEN-0001? Or it is better to use Prefix and IssueID for combining? In that case the issues will not seems to be in order like IT-0001, GEN-0002 (instead of GEN-0001)? 2) What about the CategoryIndex? How can I create it during creating an issue? I meant that when creating an issue I will need to get the max number for this field and plus one? Or any other way that I should follow? Thanks. – Jack May 05 '15 at 13:30
"Should I combine the Prefix and CategoryIndex in order to show the issue on any page": It depends on what you want to do: have a unique counter or 1 per category – Sébastien Sevrin May 05 '15 at 13:31
"How can I create it during creating an issue? I meant that when creating an issue I will need to get the max number for this field and plus one?": Yes you would have to put that logic somewhere in the database (trigger, SP) or the application (be careful about concurrency). The idea would be to select the max CategoryIndex for a category. This may be worth another question on SO. – Sébastien Sevrin May 05 '15 at 13:35
Ok, thanks. What about using GUID instead of Int for id field? I think it is not good for the id numbers to be generated in order? Is not it? or is there any reason to use or not to use GUID for this scene? – Jack May 05 '15 at 13:53
1

I would go for an `int`, but again it depends on what you prefer regarding the context. [Here](http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx) and [here](http://stackoverflow.com/questions/21896782/using-int-or-guid-as-primary-key) you can find pro and cons for `int` and `guid` – Sébastien Sevrin May 05 '15 at 13:58
1

If you have less than 2 billions records - use int. [Here](http://stackoverflow.com/questions/504905/is-it-better-to-use-an-uniqueidentifierguid-or-a-bigint-for-an-identity-column) some explanation – teo van kot May 05 '15 at 14:03
Ok, int is better for me. The last problem regarding to this issue is that: how can I create id numbers in order i.e. 1, 2, 3, 4 without any gap? I think it is not good idea to retrieve the max id just before creating a new item? Any idea? Thanks in advance again... – Jack May 05 '15 at 14:35
1

Please create another question for this, There are really to many comments on this answer. – Sébastien Sevrin May 05 '15 at 14:45
@AndreCalil Many thanks for your help. In that case the only option to achieve this seems to be using Stored Procedure on the Database side. Is there any other option to solve this problem on the program side? I use MVC and Entity Framework in my application and it would be perfect if I could make this on SaveChanges() method, or something like that. – Jack May 07 '15 at 06:13
@H.Johnson Simply enough. Override `SaveChanges` and if the newly added object is an `Issue`, you execute that query to fetch the CategoryIndex and update it before commiting. You can execute SQL queries with EF using `Context.Database.SqlQuery(query, params)`. – Andre Calil May 07 '15 at 11:27

Combining Characters with Id Field

2 Answers2