CODEWITHSUNDEEP
c#sql-serverasp.net-mvcasp.net-mvc-5guid
Jack
Jack

Reputation: 1

Combining Characters with Id Field

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.

Upvotes: 0

Views: 346

Answers (2)

Sébastien Sevrin
Sébastien Sevrin

Reputation: 5405

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

Upvotes: 2

teo van kot
teo van kot

Reputation: 12491

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.

Upvotes: 3

Related Questions