Can I create a many to many relation using a junction table with a junction table

I want to create a m2m relation between two tables and one of the table is a junction table for m2m relation. So basically, I have 3 tables and 2 can be joined using a m2m. But in order to join three tables, I have two options, one is to create a m2m with the third table and the already created junction table and the other approach is to create a table which has foreign key of all the three tables. Which of the above approach is suitable.

The two approaches to implement table with relation to three tables are:

CREATE TABLE PromoPrizeScreen (
    id SERIAL PRIMARY KEY,
    prize_cap INTEGER DEFAULT NULL,
    prize_used INTEGER DEFAULT NULL,
    promo_prize_id INTEGER NOT NULL,
    screen_id INTEGER NOT NULL,
    FOREIGN KEY (promo_prize_id) REFERENCES PromoPrize (id) ON DELETE CASCADE,
    FOREIGN KEY (screen_id) REFERENCES Screen (id) ON DELETE CASCADE
);



CREATE TABLE PromoPrizeScreen2 (
    id SERIAL PRIMARY KEY,
    prize_cap INTEGER DEFAULT NULL,
    prize_used INTEGER DEFAULT NULL,
    promo_id INTEGER NOT NULL,
    prize_id INTEGER NOT NULL,
    screen_id INTEGER NOT NULL,
    FOREIGN KEY (promo_id) REFERENCES Promo (id) ON DELETE CASCADE,
    FOREIGN KEY (prize_id) REFERENCES Prize (id) ON DELETE CASCADE,
    FOREIGN KEY (screen_id) REFERENCES Screen (id) ON DELETE CASCADE
);

I am implementing this in Django so these are the django models:

class PromoPrizeScreen(models.Model):
    id = models.AutoField(primary_key=True)
    prize_cap = models.IntegerField(default=None)
    prize_used = models.IntegerField(default=None)
    promo_prize = models.ForeignKey(PromoPrize, related_name='promo_prize_screens', on_delete=models.CASCADE)
    screen = models.ForeignKey(Screen, related_name='promo_prize_screens', on_delete=models.CASCADE)

class PromoPrizeScreen2(models.Model):
    id = models.AutoField(primary_key=True)
    prize_cap = models.IntegerField(default=None)
    prize_used = models.IntegerField(default=None)
    promo = models.ForeignKey(Promo, related_name='promo_prize_screens', on_delete=models.CASCADE)
    prize = models.ForeignKey(Prize, related_name='promo_prize_screens', on_delete=models.CASCADE)
    screen = models.ForeignKey(Screen, related_name='promo_prize_screens', on_delete=models.CASCADE)

Вернуться на верх