What is the correct way to specify a record as the *current* record?

Question

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.

Answer 1

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.

Answer 2

I've seen several approaches to this:

• a boolean (if the DBMS of choice supports that) or character (e.g. 'Y','N') indicator column;

• a more general "status" column, containing values like 'ACTIVE','HISTORY','EXPIRED' etc.

• a pair of timestamp columns (e.g. START_DT, END_DT), indicating the range of dates when a particular record is (was) active. The current record would have the END_DT value in the future, like '9999-12-31', which makes querying the current record simple: ...where current_date between START_DT and END_DT

• a history table where all non-current records would be moved as soon as they become non-current. If you're only interested in current records, you would query the current table, otherwise you'd query a view that UNION ALLs both current and history tables.

In the first three cases you would need to ensure consistency via some sort of a constraint, if your application is unable to do that.

Which approach you choose depends on what constitutes the current record in your particular case.