1

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)

schema

  • 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?
Improve this question
9
  • 1
    Similar question: How to have a one-to-many relationship with a privileged child?
    – ypercubeᵀᴹ
    Commented May 4, 2018 at 8:58
  • 1
    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ᵀᴹ
    Commented May 4, 2018 at 9:00
  • 2
    You can make the foreign key deferrable, then you can define activeshipid as not null and still insert both rows in a single transaction
    – user1822
    Commented May 4, 2018 at 9:06
  • 1
    You can even make it work with non-deferrable FKs, using modifying CTEs, inserting into both tables in one statement.
    – ypercubeᵀᴹ
    Commented May 4, 2018 at 9:15
  • 1
    @EvanCarroll but it's not image of code, it's image of schema.
    – Konrad
    Commented May 4, 2018 at 9:45

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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