ASP/SQL Database question

Is there anyway of putting a prefix on an primary key field? I'll try explain with an example.

tblCodes

CodeID [PK] - Integer
CodeName
CodeDesc
CodeType

When a new code is created the ID is simply the next value as you would expect. To help with identifying the codes in my actual application, I would like the ID to be based on the CodeType.

For example: There are four types of code (red, green, blue, orange), if when creating a new code the user selects the type red, the CodeID will be "RED\1". If another is made using the type red, it will become "RED\2". The same applied the the others, a green code will have a prefix of "GREEN\" which increments.

Really not sure how to go about doing this, maybe a seperate table for CodeTypes is needed? I'm a novice programmer and i'm also new to SQL to please to be gentle!

There is and only needs to be 4 code types, if that's any help.

To give a bit more information on the reasoning for wanting the prefix on the CodeID.

Scenario
A user is inputting the amount of time he has spent on a code. There is a drop down value which he must select the CodeID from. At the moment there is no distinguishing between the CodeTypes, so he will just see 1, 2, 3 ,4 ,5.

If I can do what i'm wanting, the user will see GREEN\1, GREEN\2, RED\1, RED\2 and so on. Making it a fair bit more user friendly.

Any ideas?

I would suggest using a separate field to store the CodeType (Red, Green etc) in addition to the ID.

This will give you the flexibility of using it in different ways e.g. concatenate in sql statement to generate strings like "Green/1" or use it separately to say group by CodeType.

|||

Ideally, you need to create another table to store user/CodeType pair to deal with Many-To-Many relationship. The table at least includes two columns: UserID and CodeId.

|||

I don't really see why you are saying I need to have UserID in there at all.

I'm still not sure how to do this :(

It's only really needed for presentation reasons. So the actual value doesn't need to be stored as GREEN\1.

For example, I want the drop down menu to show GREEN\1 or RED\12 simply to make it easier for the user to tell what time of code his is picking, rather then all the codes looking the same but just with different numbers (1, 2, 3, 4, 5, 6).

Any ideas?