December 30, 2019 at 5:47 pm
Hello All,
I have not been here on Central for a bit. The website looks great !!.
Most of my SQL Career has been spent querying data for reports. Recently I decided I wanted to learn front end development. So I have been watching tutorials on Blazor/ C# etc. I have now gotten to the point of CRUD operations and creating my application. I have a concept of what I think a User/Login schema should look like but I would like any advice on my current table structure and relationships. I have a thick skin so if I'm missing a best practice please let me know or if there is a better way to do it.
Thanks Again !
CREATE TABLE [Application_User].[Profile]
(
UserProfileId INT IDENTITY (0,1) PRIMARY KEY -- Id /Username to be used for logging in and through the application
,UserProfileName VARCHAR(30) UNIQUE
,Prefix VARCHAR(10)
,FirstName VARCHAR(20)
,MiddleName VARCHAR(20)
,LastName VARCHAR(20)
,Suffix VARCHAR(10)
,DateOfBirth DATE
,Email NVARCHAR(255)
,PhoneNumber1 VARCHAR(24)
,PhoneType1 VARCHAR(10)
,PhoneNumber2 VARCHAR(24)
,PhoneType2 VARCHAR(10)
,PreferredPronoun VARCHAR(12)
,Address VARCHAR(100)
,City VARCHAR(30)
,State VARCHAR(3)
,ZipCode VARCHAR(10)
,Country VARCHAR(30)
,IsActive BIT
)
CREATE TABLE [Application_User].[Login]
(
UserProfileId INT PRIMARY KEY
,UserProfileName VARCHAR(30) UNIQUE
,PasswordHash CHAR(60)
,PasswordSalt CHAR(60)
,IsLocked BIT
CONSTRAINT FK_UserProfileId FOREIGN KEY (UserProfileId)
REFERENCES [Application_User].[Profile](UserProfileId)
)
/*
Tracks login history.
*/
CREATE TABLE [Application_User].[LogHistory]
(
LogHistoryId BIGINT IDENTITY (1,1) PRIMARY KEY
,UserProfileId INT
,LoginDate DATETIME
,LogoutDate DATETIME
,Attempt BIT --Successful/Failure
,WasLocked BIT
CONSTRAINT FK_ProfileId FOREIGN KEY (UserProfileId)
REFERENCES [Application_User].[Profile](UserProfileId)
)
/*
Tracks changes to any of the Application_User tables.
*/
CREATE TABLE [Application_User].[ChangeAudit]
(
ChangeAuditId INT IDENTITY (1,1) PRIMARY KEY
,TableName VARCHAR(255) --The table effected
,TableKey INT ---The Primary Key of the effected record
,ChangeField VARCHAR(255) -- The field effected
,ChangeEvent VARCHAR(MAX) --- A description of what was changed to what
,EventDate DATETIME -- The Date and Time of the record change
,EventType CHAR(1) --Delete, Create, Update (D,I,U)
,UserProfileId INT -- The account that made the change
)
***SQL born on date Spring 2013:-)
December 30, 2019 at 6:04 pm
Hi,
I am little confused on UserProfileId being used as primary key in both the tables [Application_User].Profile and [Application_User].Login. In my opinion, it should be used as primary key in either of them (probably in [Application_User].Profile) and foreign key in the other. Again, in [Application_User].ChangeAudit, the data type for the same column (UserProfileId) has been changed to varchar(30). Any reason for this?
December 30, 2019 at 6:38 pm
The first part i was thinking i wanted to keep a 0..1 relationship but i may be a 100% wrong on that idea and should do the a more traditional approach as your saying. The changed data t ype was a mistake. I'll edit now.
Thanks !
***SQL born on date Spring 2013:-)
January 14, 2020 at 2:08 am
Is there an option for a profile to be associated with more than one login, and vice versa? I'm guessing no, in which case I am curious why you would want to maintain a second table for what is essentially the same entity. The login fields could be added to the profile table and maintained as a single record for the single entity.
If you do want to keep them separate, might I suggest not having UserProfileName on both, because you could end up in a scenario where these are different for the same UserProfileId record?
Also, what is the minimum supported SQL version you're targeting? If SQL 2016 or higher will be your minimum, consider Temporal Tables instead of maintaining a custom ChangeAudit table. Basically, temporal tables will retain the history of a table for all data modifications but it doesn't require all the extra coding and checks required to maintain this yourself and avoids the performance pitfalls that often come with custom auditing like this.
The column name Attempt in LogHistory is a little misleading - it appears to be the outcome, so the name "attempt" may confuse users.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply