I want to store active entity in one of my tables let's say I have 2 tables:
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?