help regarding auto increment

greedyisg00d

I have a table employee where EmployeeID field is set as primary key. How can I create a custom auto increment for my EmployeeID for example:

EmployeeID
SDD00001
SDD00002
SDD00003

Thus when new records are inserted it will automatically increment just like in the example.
Any idea on how can this be done. Thanks

NogDog

I would just use an auto-increment integer as the primary key field. If the letters you want at the beginning can vary, then that would be a separate column; if it's always "SDD" then you can add it in the script. Then you can build the ID string whenever you need it, either within the query with the DBMS's string functions or in PHP with sprintf() or printf():

$row = mysql_fetch_assoc($queryResult);
$idString = sprintf('SDD&#37;05d', $row['id']);

Roger_Ramjet

Personally I would create an Insert Trigger that calculated the full EmployeeID from the autoinc field and stored it in a second field.

In the long run this is going to save time and errors when applications need to use the full employee id: write once - read many.

You get the best of both worlds then, the autoinc value takes care of itself and the primary key is an integer which is most efficient for indexes and table joins, while users and programmers can search on and see the required identifier without the need for any calculation or data manipulation.

johanafm

I'd still advocate against storing redundant data in this case.

SELECT CONCAT(stringPart, integerPartWhichIsAlsoPrimaryKey)

or simply

SELECT stringPart, integerPartWhichIsAlsoPrimaryKey

from which the full identifier is still easily seen.

Roger_Ramjet

johanafm;10926128 wrote:
I'd still advocate against storing redundant data in this case.
SELECT CONCAT(stringPart, integerPartWhichIsAlsoPrimaryKey)
or simply
SELECT stringPart, integerPartWhichIsAlsoPrimaryKey
from which the full identifier is still easily seen.

Sure, except that:
Every application programmer who uses this data is going to have to get that right and you've no guarantee that they will.
Every time the database is queried this processing has to be done, consuming memory and processor cycles.
This is NOT redundant data because the Employee ID is a real world data element while the autoinc column is an internal database convenience with no external meaning. It just happens to be a convenient way of generating a unique employee id which does have meaning.
It is not dynamic data and will not be updated so the usual cautions about redundant data are irrelevent here.

johanafm

I see. I got the impression you'd store string, int, and string+int, which I'd still be against.

As for the application programmers, a properly designed class would deal with retrieving the indeitifer, thus eliminating errors on behalf of application programmers (once the class is in place).
However, I still doubt "return $string . $int" makes enough of a difference over "return $stringint" to worry about it.

And where from comes the asumption it's static data? If several different strings are in use, they have to carry some meaning or they'd be absolutely pointless.

Roger_Ramjet

johanafm;10926152 wrote:
I see. I got the impression you'd store string, int, and string+int, which I'd still be against.

As for the application programmers, a properly designed class would deal with retrieving the indeitifer, thus eliminating errors on behalf of application programmers (once the class is in place).
However, I still doubt "return $string . $int" makes enough of a difference over "return $stringint" to worry about it.

And where from comes the asumption it's static data? If several different strings are in use, they have to carry some meaning or they'd be absolutely pointless.

1 never assume that applications programmers will a) use your class b) use it correctly c) not modify your class.
Anyway, I was talking more from the point of view of someone querying it, say for MI reports. No php class involved there so each report query is going to have to do the processing and that is something that is easy to forget. After 30 years at this, the one thing that I am sure of is that you do not want to be laying booby-traps for the fellow that follows you.

2 it is NOT 'return $string . $int' it is 'return $string . padzeros($int)' so you need a function 'padzeros()' to pad the integer with leading zeros so that you get your EmployeeID in the correct format. Tha is a significant processing overhead

3 EmployeeID is pretty much going to be static in the real word, now isn't it.

johanafm

1 & 2. Good points.

I'm talking about the "string part". Now, I have no idea what use it has, but my first guess would be that it signifies deparment, faculty, project or similar. And as such, that part will not be static, even though it may not be likely to change on a regular basis for any given record. So when combining it with an auto incremented value, that means this value is no longer static either.
But one other concern I had about it is getting the information it carries, since that involves processing string comparisons.

I really appreciate your feedback 🙂