SQLITE inheritance or how to solve multiple foreign keys-CodePudding

I've seen multiple solutions for this already, but they seemed not to be perfectly suited for my case or at least I am still hoping for a better solution.

In my database there are the following tables "places", "animals" and "people". Currently I have tables "hasAnimal" and "hasPeople", but a polymorphic approach "hasBeing" would be more reasonable. But I would like to link the foreign-keys; but one can't link two foreign keys - as far as I can see.

How should I resolve the polymorphic issue?

I don't know much about databases, so the solution might be obvious.

I will now add some more info in case that my written question/explanation doesn't contain enough information

CREATE TABLE "persons" (
    "id"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "firstname" TEXT NOT NULL,
    "surename"  TEXT
)

CREATE TABLE "animals" (
    "name"  TEXT NOT NULL UNIQUE,
    "description"   TEXT NOT NULL,
    "movementLocations" INTEGER NOT NULL,
    "movementPlaces"    INTEGER,
    "groupsize" INTEGER NOT NULL,
    "link"  TEXT,
    PRIMARY KEY("name")
)

CREATE TABLE "places" (
    "name"  TEXT NOT NULL UNIQUE,
    "description"   TEXT NOT NULL,
    PRIMARY KEY("name")
)

CREATE TABLE "hasPerson" (
    "id"    TEXT NOT NULL,
    "place" TEXT NOT NULL,
    FOREIGN KEY("id") REFERENCES "persons"("id"),
    FOREIGN KEY("place") REFERENCES "places"("name"),
    PRIMARY KEY("id","place")
)

CREATE TABLE "hasAnimal" (
    "name"  TEXT NOT NULL,
    "place" TEXT NOT NULL,
    PRIMARY KEY("name","place"),
    FOREIGN KEY("name") REFERENCES "animal"("name")
)

And the wouldbe-table (Being would be the parent of animal and person)

CREATE TABLE "hasBeing" (
    "name"  TEXT NOT NULL,
    "place" TEXT NOT NULL,
    PRIMARY KEY("name","place"),
    FOREIGN KEY("name") REFERENCES "animal"("name") OR "person"("id")
)

Why do I think it is better to use "hasBeing"? By that I am not using two tables for describing that a being is at a place, but can use one instead. In order to get all beings at a place I no longer have to run through 2 tables. If the assumption "bringing together what belongs together and merging similar relationships is good" is wrong, I will accept the explanation as an answer and rephrase the question.

CodePudding user response：

Your has* tables are called Link tables, in that they link two entities in your database together. If one entity is linked to two others, each of the others needs an FKey as you had in your original.

Now you are introducing a a new entity: a Being, but only as a link and there is no actual Being entity. This is not a good design. You would be better off with the original or:

hasBeing has a both person and animal names as separate nullable columns, with FK's to their tables and one of the two names will always be null.
Merge Persons and Animals into one table, with a flag indicating what sort of entity each row represents. (Not a good idea as there is not much commonality of data between the two, but it can be a useful technique.)

The original way is probably best, and is far from uncommon. Remember, a Relational database is not object oriented and polymorphism is not an appropriate aim.