Reference to only one of multiple possible tables

Question

I'm moving my poorly designed SQLite database to the Peewee ORM and having troubles finding a nice way for the following scenario.

I have a DVD table to hold all information about the release itself and tw tables describing a DVD status. Both these tables have specific details for their status. Code example:

class DVD(BaseModel):
    title = TextField()
    # More info like this

class Sold(BaseModel):
    date = DateField()
    buyer = TextField()

class Onloan(BaseModel):
    loaned = DateField()
    back = DateField()
    person = TextField()

How would I properly link only one status table to the DVD object so I can access the status data through DVD.status for example?

score 0 · Answer 1 · answered Dec 13 '14 at 18:40

You would add a foreign key.

If a DVD can only ever have one Sold status, for instance, you would write:

class Sold(BaseModel):
    date = DateField()
    buyer = TextField()

class DVD(BaseModel):
    title = TextField()
    sold = ForeignKeyField(Sold, related_name='dvds')

On the other hand, if a DVD can be loaned multiple times, you might write:

class DVD(BaseModel):
    title = TextField()

class Onloan(BaseModel):
    loaned = DateField()
    back = DateField()
    person = TextField()
    dvd = ForeignKeyField(DVD, related_name='loans')

Sure, but what about multiple status tables for your first example? I would add a onloan foreign key too, but only one of those can be "active" at the same time. Is some client side code the only way to handle this? Or should I just use one table with all columns combined for multiple statusses? — Timo, Dec 14 '14 at 13:59

Reference to only one of multiple possible tables

1 Answers1