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?

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.