Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.
For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.
For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.
Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.
Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.
The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.
Taking all of this into consideration, I would recommend Option A.
I hope this helps,
Matt
Your points are unrelated to database design: choice of natural or surrogate key is an implementation decisions after conceptual and logical models are complete
In addition to comments and other answers:
- some natural keys work well such as currency or language codes (CHF, GBP, DE, EN etc)
- avoiding composite keys forces you to always join intermediate tables (rather than simple) parent-grandchild
- adding a surrogate key in unnecessary for link tables
Edit: example of "composite keys"
Assume: t1 has child t2 has child t3
If you had the key of t1 in t3 (composite key) you can join t1 and t3 directly.
t1 key is also the left hand column of t3 key so you don't need an extra index
With a surrogate key/FK, you have to join via t2
You need extra indexes on the FK columns in t2 and t3 which
This latter option with the "always use surrogate key" dogma
- adds complexity
- decreased or reverses disk space "savings"
Best Answer
TLDR; firstly no it doesn't make sense to do that, secondly you can do that and the application wont care / notice either way
The main thing that you are fighting with confusing is human interaction, So as far as any application is concerned you should have the
user_id,employee_idand aleave_request_id, any application when coded correctly will be able to identify the differences,Looking at it, would it not be easier to have a
persontable (I'm presuming here thatusersare external to your employee list.From here you could have a person and all their details in that table, with the identity field on it, and then if they are a user that entry is added into that table, if they are an employee they are entered into that table, (if both then both tables) and finally if an employee leaves they can be added into that table.
Now you have one id across all of your tables which makes joining them together a lot easier if you need to look at any users who are employees who have left you have a single ID across all tables, it also means that from a human perspective the ID you know what it all is in relation to because its all the same.