Primary Key as domain username AND userID?

Question

I have read here that it is probably better to use an AI integer to denote users as opposed to an assigned domain (string) username. I can accept that, however, when I am including in the 'users' table the auto generated userid and the unique domain usernames, how should I denote that in other tables as FKs? eg. Department table with 'userid' AND 'domainname' or just userid; hardware table with 'userid' AND 'domainname' or just 'userid' as owner?

Answer 1

Just the user-ID. This is a key component of database normalization; if the other tables contain any other information that can be completely determined by the user, then you introduce the risk of inconsistencies whereby different records make different claims about a given user.

Answer 2

I'll give you some Idea but not exact answer. -"this answer is base on my understanding in your question"

Design example :

Table Users:

Your primary key here is UserId and you can use this as foreign key in other table.

UserId | Usernames 

Table Department:

Your primary key here is DepartmentId and you can use this as foreign key in other table.

You also have a foreign key called UserId from table Users

DepartmentId | UserId |DomainName

Table Hardware:

Your primary key here is HardwareId

You also have 2 foreign keys called DepartmentId and UserId

HardwareId | DepartmentId | UserId (optional) | Etc...

OK, Each table must and required to have a own primary key and you can't use the DepartmentId and UserId to become primary key in table Hardware because they are foreign key in this table.

DepartmentId - Include, so you will know the department where the hardware base.

UserId - (Optional), You will know the owner or user of this hardware by using only the UserId