How to implement a one-to-many relationship with an "Is Current" requirement

Question

Designing a database, there's a relationship between two tables, Job and Document. One Job can have multiple Documents, but one (and only one) of these Documents needs to be flagged as IsCurrent. This is not always the most recent Document associated with that Job.

Structurally, I can see two ways of doing this.

The first is to add a DocumentId column to Job, and a JobId column to Document. This will work, but creates a circular reference: when imported into Entity Framework you end up with the peculiar situation that a Job has both a Document and a Documents collection. Likewise that Document has both a Job and a Jobs collection.

The second is to add an IsCurrent bit flag to the Document table. This will work, but leaves it logically possible for a Job to have multiple IsCurrent Documents, which is not allowed.

Questions:

1) Am I right in thinking there's no "third way" out of this dilemma?

2) Presuming not, which is better, and why? I favour the second solution as it seems much cleaner and we can enforce the single IsCurrent through the business logic. My colleague favours the former solution because it results in simpler C# code and object references - if we rename the foreign keys, it should avoid the confusion created by Job/Jobs.

score 3 · Accepted Answer · answered Jun 04 '15 at 07:18

3

If your back-end is SQL Server, you can create a filtered index to ensure that each job has at most one current document:

CREATE UNIQUE INDEX IX_Documents_Current
    ON Documents (JobId) where IsCurrent=1

That way, it's not just enforced at the business level but is also enforced inside the database.

answered Jun 04 '15 at 07:18

Damien_The_Unbeliever

234,701
27
340
448

That's great, thanks. I thought filtered indexes were performance tools - I had no idea they could be used to enforce integrity. – Bob Tway Jun 04 '15 at 08:13
@MattThrower - if you think about it, *any* unique index has at least some aspect of enforcing integrity. – Damien_The_Unbeliever Jun 04 '15 at 08:26
so simple, so elegant... brillant and so on: always read **all** the documentation of the tools one use. – tschmit007 Jun 04 '15 at 08:32

tschmit007 · Answer 2 · 2015-06-03T16:43:10.457

just for a third way (and for fun): consider using not a bit, but an int equals to max + 1 among the documents of the job.

then create a unique index on {job FK, said int}.

you can:

change current by updating the int,
get the current by searching the max and
prevent to have more than one current because of the unique index.
create a new non current document by using min - 1 for said int.

this is not the simplest to implement.

score 1 · Answer 3 · answered Jun 04 '15 at 07:13

Yes there is a third way out of this dilemma. You need a DBMS that supports SQL's CREATE ASSERTION (and supports it correctly, of course). With such a DBMS, you can declare any data rule that applies to your situation and your DBMS will enforce that rule for you.

Unfortunately, no such DBMS exists * in the SQL world *. Outside of the SQL world, there are such engines. ASSERTIONs being my hobbyhorse, I wrote one myself. If you're interested, a Google search should lead you to it quickly.

How to implement a one-to-many relationship with an "Is Current" requirement

3 Answers3

Linked