Perhaps doing less in the user table and adding an auth table?<p>Split out the internal_id and external_id from the auth_id and dates.<p>Of course, this assumes you are controlling the input of the value for the external_id column. In the example below, the external_id is populated by calling a function to get a cuid version 2 ID by default. You may have some other mechanism that gives you a unique external_id.<p>Here is an example (in Postgresql):<p>create table users_hn
(internal_id int primary key generated always as identity,
external_id text not null default cuid2(''::character varying, 12, 'hackernewsusersxmpl'::character varying), -- or whatever method to generate an external_id
display_name text,
access_level int not null
);<p>create table users_hn_auth
(users_hn_auth_id int generated always as identity,
users_hn_internal_id int,
auth_id text not null,
start_date timestamptz check(start_date >= ((current_timestamp::date)::timestamp)),
constraint fk_users_hn_internal_id foreign key (users_hn_internal_id)
references users_hn(internal_id)
);<p>/* Intial insert of new user. Only generate this user row once. <i>/
with insert_user_hn as
(
insert into users_hn(display_name, access_level) values ('John Doe 1', 1)
returning internal_id
)
insert into users_hn_auth(users_hn_internal_id, auth_id, start_date)
select insert_user_hn.internal_id, 'johndoe1@example.com', current_timestamp
from insert_user_hn
;<p>/</i> Update user auth table with new data, for a specific user. All changes to auth_id and when that event occurs are recorded here. <i>/
insert into users_hn_auth(users_hn_internal_id, auth_id, start_date)
--values (2, 'johndoe1@example.com', current_timestamp)
--values (2, 'jdoe1@example.com', current_timestamp)
--values (2, 'johnd1@example.com', current_timestamp)
values (2, '(555) 555-1234', current_timestamp)
;<p>/</i> Get user info
This query will need changes as more users are inserted into the table. A function with parameter of the internal_id to retrieve a specific user, for example.<p>*/
select u.internal_id, u.external_id, u.display_name, u.access_level,
a.users_hn_auth_id, a.auth_id, a.start_date
from users_hn u
join users_hn_auth a
on u.internal_id = a.users_hn_internal_id
and a.start_date = (select au.start_date
from users_hn_auth au
group by au.users_hn_internal_id, au.start_date
order by au.start_date desc
limit 1
)
;