The correct way to specify a record as the current record

database-designdesign-pattern

The two options I have thought of is either to add a column boolean column called Current onto the table; but I'm sure that would violate some level of normalisation due to more than one record being able to be set as current.

Alternatively having another table that specifies the ID of the current record, but this also doesn't seem like a great way of doing it.

Is there a better way or convention for achieving this kind of thing?

Edit:
I probably should of said there can only be one current record, so when it is changed the previously current record needs it's 'current' status removed. The system isn't complicated enough to need to worry about concurrency or anything like that. The main reason for the question is that I wanted to do things in the correct/conventional way if there was one.

I believe the best way is to have a settings table that will hold the ID of the current record.

Best Answer

Assuming that each login can have a 'current record' you might have a table something in the form of:

CREATE TABLE dbo.CurrentRecords
(principal_sid varbinary(85),
 current_id int);

However, the maintenance of this table and cleaning up after it will be necessary.

If the 'current record' is based on the client application, perhaps just persisting that in memory and passing the current_id (or equivalent) into any queries, procedures, and so on that you use.

If you are concerned about concurrency, then use the appropriate locks to control that piece of the picture.

EDIT: Based on your update, since there is one single 'current record' then I would concur that a one row table with the current record id is sufficient.

Related Solutions

Mysql – Does the table violate normalization rules

don't add more stat columns unless you are absolutely sure the required # of columns wont grow, tables tend to get unmanageable that way.

if you need to work with the individual stats in your comma separated text field, you probably should break them off into their own table where each row has 1 stat, make guid+statid your primary key and then you can make the stat column something more reasonable like a varchar (20). This might take up a little more diskspace but it should be faster working with individual stats...

Sql-server – How to represent class table inheritance (current DBMS-specific way please)

Ultimately, I implemented ypercube's suggestion from comments:

The "type" column can be defined as a computed (but constant) column in SQL-Server. I think it has to be PERSISTED though so it can participate in the Foreign Key constraint.

This worked well both for performance and compatibility with with my tools (Entity Framework <= 6.0):

CREATE TABLE [dbo].[Account](
    [Id] [int] IDENTITY(1000,1) NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole] [int] NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
CONSTRAINT [UX_Derived_Relation] UNIQUE ([Id], [CommunityRole]))

CREATE TABLE [dbo].[Recruiter](
    [Id] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole]  AS ((1)) PERSISTED NOT NULL,
    [RecruiterSpecificValue]  [int] NOT NULL,
FOREIGN KEY ([Id], [CommunityRole]) REFERENCES Account([Id], [CommunityRole]))

CREATE TABLE [dbo].[Candidate](
    [Id] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole]  AS ((2)) PERSISTED NOT NULL,
    [CandidateSpecificValue]  [int] NOT NULL,
FOREIGN KEY ([Id], [CommunityRole]) REFERENCES Account([Id], [CommunityRole]))

This mapped well to my implementation of multiple discrete account types, Recruiter and Candidate, on my Job Board.

Best Answer

Related Solutions

Mysql – Does the table violate normalization rules

Sql-server – How to represent class table inheritance (current DBMS-specific way please)

Related Question