How to create a 1:N and 1:1 relation within the same table?

Asked

Viewed 1,624 times

1

I have a table for recording images that is used as a foreign key for other tables. The problem is that in one of these tables (PROMOCOES) the ratio is 1:N (a promotion for N images ), while in the other tables the ratio is 1:1. My question is on how to organize these tables because I cannot use foreign key within the table of images. This way it would be better to create a specific table of images of promotions, or create an intermediary table between PROMOCOES and images and continue using a single table of images?

sql

1 answer

3


For standards rules You should have a foreign key in the picture table. But if you can’t have that key you can create a table that links images and promotions by continuing to use the table of images.

+----------+-----------+
| promo_id | imagem_id |
+----------+-----------+

It is the simplest way to do, this is clear if you can’t even put foreign key in the table pictures. And don’t forget to put as key primary the 2 columns of this table.

  • 1

    The problem with using a foreign key is that you would need a key for each table that uses an image.

  • Welcome to Stackoverflow EN! Yes, I noticed this. I when it happens I create a table that relates the 2 tables, but no foreign key.

  • Thank you, that seems to be best practice.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.