CODEWITHSUNDEEP
sql-serverdatabasedatabase-designrelational-database
1110
1110

Reputation: 6829

Need advice on table relations

I have a table Users:

    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](20) NOT NULL,
    [Email] [nvarchar](100) NOT NULL,
    [Password] [nvarchar](128) NOT NULL,
    [PasswordSalt] [nvarchar](128) NOT NULL,
    [Comments] [nvarchar](256) NULL,
    [CreatedDate] [datetime] NOT NULL,
    [LastModifiedDate] [datetime] NULL,
    [LastLoginDate] [datetime] NOT NULL,
    [LastLoginIp] [nvarchar](40) NULL,
    [IsActivated] [bit] NOT NULL,
    [IsLockedOut] [bit] NOT NULL,
    [LastLockedOutDate] [datetime] NOT NULL,
    [LastLockedOutReason] [nvarchar](256) NULL,
    [NewPasswordKey] [nvarchar](128) NULL,
    [NewPasswordRequested] [datetime] NULL,
    [NewEmail] [nvarchar](100) NULL,
    [NewEmailKey] [nvarchar](128) NULL,
    [NewEmailRequested] [datetime] NULL

This table has 1 to 1 relation to Profiles:

    [UserId] [int] NOT NULL,
    [FirstName] [nvarchar](25) NULL,
    [LastName] [nvarchar](25) NULL,
    [Sex] [bit] NULL,
    [BirthDay] [smalldatetime] NULL,
    [MartialStatus] [int] NULL

I need to connect user to the all other tables in database so is it better to:
1) Make relations from Users - to other tables?
2) Make relations from Profiles - to other tables?

Upvotes: 0

Views: 61

Answers (2)

supergrady
supergrady

Reputation: 1322

If the PK of Profiles is a FK to Users, I would maintain consistency and use Users as the parent table in other relationships across the database.

However, if it is a true one-to-one and not a one-to-zero or one relationship, it doesn't matter.

Another consideration is how the data in this database is accessed by any applications. Do the applications use an OR/M like Entity Framework which is aware of FK relationships? If so, consider using whichever table has columns which will most commonly be accessed by queries based on the child tables. For example, an application might display Profiles.LastName and Profiles.FirstName all over the place and very rarely read anything from the Users table. In this situation, you will save your database some I/O and save your developers some keystrokes by building relationships off the Profiles table.

Upvotes: 0

Eric J. Price
Eric J. Price

Reputation: 2785

Since the table [Users] contains the Identity value and is therefore where the [UserID] value originates, I would create all the foreign keys back to it. From a performance standpoint, assuming you have your clustered index on both tables set on the [UserID] column there should be very little performance impact.

Technically I suppose the [Users] table could contain more data per row and therefore the index could span more pages and you could have milliseconds difference in lookups, but I think it makes more sense to relate it back to the table that created the [UserID] and is similarly named. That said, you can really do either.

Upvotes: 2

Related Questions