As Grant stated, it's a huge subject, so I'll just limit (at least for now) most of this to a "User" table similar to what the OP asked about.
First, the idea of using a surrogate key, such as an IDENTITY column, seems essential for this type of table to me. Yes, you could use a SEQUENCE but then, in order to guarantee a correct sequence value, you would have to either write any and all code to use a sequence or you need to write an INSERT trigger to guarantee that a correct sequence number is used. Writing all code to use a correct sequence number is fine but that won't protect you when it comes to ad hoc inserts. You could also protect against ad hoc inserts but now we're starting to get complicated. An IDENTITY column seems to be the perfect choice for the PK.
On to your question... and, yeah... a whole lot of people will have different opinions on this subject and so consider this to be my opinion.
If you store historical rows in the User table, that means that you'll be inserting new rows in the table instead of updating existing rows. That means that to preserve the UserID (AppUser_ID according to the OP's table), which is the PK, you'd need yet another column to do so AND you'd have to make sure that it was populated with the value of the IDENTITY column the first time that a row for the user was created. That also means the second column would sometimes have to be treated as the PK along with some indication (start and end date of version of the row, my preferred method in an SCD TYPE II fashion if the table is going to be used for both current and historical data), maintain a version column, or have an IsCurrentRow flag column (which I never use because I need the start and end date columns anyway).
And then you have the UserName column, which must be unique if that's what it's truly being used for. What will happen when a female user wants to change her last name to her husband's surname but wants to keep the same user name? Yes, I agree that there are ways around that. I've just not found it to be worth it in the long run for other reasons, as you'll see below.
As you suggest, having current row and history rows in the same table does make reporting easier: There's no need to have a report be smart enough to look at one table, the other, or both depending, of course, on the purpose and type of report. The reports DO have to be smart enough to use something like IsCurrentRow to look at only current, only history, or both and that shouldn't be a problem. I can't speak for anyone else but, even for a table such as this, there are people writing ad hoc reports (yeah... big mistake but it's like fighting city hall... I'd have to burn it down to win that fight) where I don't actually want them to have access to historical rows. I can certainly fix that with row level security in the form of a couple of pass-through views and grant individuals read privs on only the views but that ends up being a pain, as well.
Because user tables don't generally get that large even when history is contained, these next couple of items may not seem to apply but I generally follow certain types of auditing rules because history is a form of audit and can be used as such if the notion strikes someone. One of the rules (at least where I work and the auditors love it) is that audit tables must be separate. If the table doesn't qualify as an audit-able tale, then no need for a separate history table, right? That absolutely correct until a bee gets into someone's bonnet and they change the classification of the table to being audit-able. I've been burned by that at other companies where I lost the battle of saying "If it has history, it's an audit-able table and it's better to be safe than sorry later".
Now, for tables that aren't going to stay as small as a user table, there are other issues. For example, let's talk about indexes. If you agree that historical rows must never change (and I'm sure you will), why should you need to do index maintenance on them? Also, I've found that there are serious advantages to having different and fewer indexes on current row information while there's also an advantage to also having different and, perhaps, more indexes on historical tables even if they're not being used for audits. Seriously reduced index maintenance is also included in that. I also don't see the need to continuously backup that which will never change and so I frequently move such historical tables to an "Archive" database (whether the table is partitioned or not... whole 'nuther discussion there). And while the "guarantee" by Managers and Developers of a given table "never getting that big" is certainly something to consider, if you combine the fact that's one of the more frequently broken guarantees and mix that with the other "guarantee" of a table never being declared "audit-able", I find it both easier and safer and provides other advantages to just follow the rule of "If history of any type is involved, it must be stored in a separate table".
There's also the occasional problem of someone writing and ad hoc (or even "good")query with an "oops" attached to it and they end up doing a table scan and if the history is large, it could provide some interference or I'll get a call from the person that issued the query about the database being slow. While a dose of pork chops and some training can help curtail that, that's just one more thing that can be avoided if I keep the current data separate from the historical data.
And, yeah... I don't know all there is to know about them, yet (I've finally found some time to start experimenting with them), but, from what I've seen so far, I'm thinking that Temporal Tables made all of that a proverbial walk in the park and they made the whole process much more secure.
I've probably left a couple of things out but that's my opinion.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems