Database design - Lookup table

how do you label your "code", se开发者_如何学Cquencial number (1,2,3,4...n)?

Example

My DeviceType lookup table holds mobile devices, and has the following attributes

Id
Code
Name

---

If you can tolerate gaps in the sequence, you can use any of the suggestions in other answers and you can also use SEQUENCE in Oracle, IDENTITY in IBM DB2 or auto-increment fields in MS SQL Server.

If, however, you absolutely need to avoid gaps, even in the concurrent environment, things suddenly become much less straightforward. In essence, you gonna have to identify the "candidate" value, then try to insert it and then be prepared to retry the whole process if a concurrent transaction inserted it before you.

BTW, could you use code as primary key and drop the Id altogether?

---

In MySQL, you can characterize fields (i.e., columns) as "auto-increment," which will update your code field as you insert news rows into the table. I believe this is common to many, if not all, SQL systems. Have a look over here for a description of the MySQL operation.

---

In postgreSQL the column type would be serial ( or bigserial ) to accomplish the same thing. Then in your inserts leave the serial column out and the db engine will auto-increment for you as Pete's excellent link said.

---

Most of us don't build tables that use an id number--which is usually sequential--and an additional code that's also sequential. There's no point to it.

If code is a sequential, unique number, drop the id column and make code the primary key.