Circular references for tracking currently active entity

Question

I want to store active entity in one of my tables let's say I have 2 tables:

Pirate
Ship

Named it like that to keep things interesting

I want each pirate to own many ships but have only one active ship. For that Ship will have PirateId column and Pirate will have ActiveShipId. Obviously for that to work one of those columns has to be NULLable depending on the order I insert new entries.

I also have IsActive column on the Ship table, ActiveShipId is supposed to be a shortcut and will possibly give me better performance - Lets suppose I want to get active ship without circular reference:

SELECT * FROM Ship WHERE PirateId = @0 AND IsActive = 1

With circular references:

SELECT * FROM Ship WHERE Id IN (SELECT ActiveShipId FROM Pirate WHERE Id = @0)

Will this kind of circular reference cause me any problems?
Is there any better way to design this schema?
Is ActiveShipId redundant when I have IsActive, or will it provide any performance benefits?

Similar question: How to have a one-to-many relationship with a privileged child? — ypercubeᵀᴹ, May 04 '18 at 08:58
And another, at SO, for Postgres this time. Erwin's answer explains how this can be accomplished, even with all columns NOT NULL: Complex foreign key constraint in SQLAlchemy — ypercubeᵀᴹ, May 04 '18 at 09:00
@ypercubeᵀᴹ Thanks! That's exactly what I was looking for but didn't know how to find it and what terms to look for. — Konrad, May 04 '18 at 09:03
You can make the foreign key deferrable, then you can define activeshipid as not null and still insert both rows in a single transaction — , May 04 '18 at 09:06
You can even make it work with non-deferrable FKs, using modifying CTEs, inserting into both tables in one statement. — ypercubeᵀᴹ, May 04 '18 at 09:15
@ypercubeᵀᴹ sounds like something complicated to accomplish — Konrad, May 04 '18 at 09:18
Please read https://meta.stackoverflow.com/a/285557/124486 and the accepted answer — Evan Carroll, May 04 '18 at 09:19
@EvanCarroll but it's not image of code, it's image of schema. — Konrad, May 04 '18 at 09:45
@LoneWolfMcQuade the image of the schema is ok, as a supplement. Much better if you posted the CREATE TABLE statements as well. — ypercubeᵀᴹ, May 04 '18 at 10:04

score 1 · Answer 1 · answered May 08 '18 at 11:39

The ActiveShipId isn't neccesary, because you already have an indicator at Ship table that says if the Ship is active or not. Assuming that a Pirate could own more than one Ship, and a Ship belongs only to a Pirate, then the you're modelling correctly (except the ActiveShipId workaround, it needs to be removed).

Also, by applying that workaround, you're ensuring that a Ship could belong to multiple Pirates. However, in case this not fit your needs, then give us more information about your modelling requirements.

Circular references for tracking currently active entity

1 Answers1