Apart from Solutions 1 and 2, if you have specific format for the id, you may want to explore
Ask Your Database for that Unique ID[
^].
BTW, I notice you put the "deprtmntname" in the user table, why? Aren't you duplicating the "deprtmntname" data every time you insert a new user. Rightfully, "deprtmntname" and "deptid" should be in one table say "dept" by itself. You should remove the "deprtmntname" from but leave the "deptid" in the user table, the "deptid" of the user table will link to the "deptid" of the dept table.
Learn more about
1NF, 2NF, 3NF and BCNF in Database Normalization[
^]
+++++[round 2]+++++
Quote:
let me explain you again I have a table named customerdetails here I have to provide a unique id to every individual customer and one more unique id I have to provide to customer on basis of departmentname. Every user registered with registration no and also with departmentid like I have more than 20 department I am giving you the example like dept1, dept2, dept3, dept4.
To implement your requirement in relational database design, you have two entities here, the user and the dept, each should have one one table of its own like this:
user table: dept table:
userid | deptid | username | userotherdetails deptid | deptname |deptotherdea
V |
|____________________________________________|
When a user registers, he is given a unique userid and his deptid value should be derived from a valid value from the dept table. To find the deptname of a user, you will link the deptid from the user table back to the dept table. In this way, the dept table acts like a lookup table.
Both the userid and deptid are primary keys for the respective tables.
If you do not understand my explanation, then clearly you have got to read up on
Introduction to database design[
^].
One thing to note is that database design is unlike excel spreadsheet. They are design principles to follows so as to minimize duplication, redundant data, data integrity, and to facilitate the manipulation of data subsequently.