Yourdata model is pretty much wrong. There is no such thing as IDENTITYin RDBMS. This is a physical count of physical insertion attempts toa physical disk that holds the a table. It has nothing whatsoever todo with RDBMS. A table is supposed to model a set, and you have justtold us you have only one employee by the name of that table! I thinkyou probably meant to have a table that models personnel.Identifiers, by definition, cannot be numeric because there is nomath to ever done on them. We need to throw out everything and startover.

Thinkabout "role_id"; what kind of role is there in this model?You are using very generic and therefore improper data element names.If you take a course in basic data modeling they would have beatenthis out of you during the first week of the class.

CREATETABLE Personnel

(user_idCHAR(10) NOT NULL PRIMARY KEY,

user_nameNVARCHAR(50) NOT NULL,

foobar_roleCHAR(4) NOT NULL

CHECK(foobar_role IN (..)),

dept_idCHAR(10) NOT NULL REFERENCES (Departments)

);

Unfortunately,this is still a mess. Have you ever read a book on RDBMS thatincluded the chapter on normalization? This table is not a realtable; users have a relationship with departments and departments arenot an attribute of a user! Think about it! Does a book grow out ofthe chest of an author? No, of course not! The author and his bookshave a relationship called authorship. Likewise personnel has arelationship with the departments to which each individual employeeis assigned (1:many relationship), called job assignment or whateveryour use it in your company.

Noticehow I use the CHECK() construct to assure data integrity. My rule isthat if the list of legal values and a constraint is short (whateverthat means currently) and static (whatever that currently means),then we use this construct. If the domain is dynamic or large, thenwe use a references. I seriously doubt that the departments in yourcompany should actually be in a references clause, but I needed toshow this for my example..

Noticehow I created the user ID as opposed to a count of physical insertionattempts on one machine, to one table on one product, etc. as you didin your original.