I have two tables Books and Audiobooks, both of which have ISBN as their primary keys. I have a table writtenby that has an isbn
attribute that has a foreign key constraint to Books and Audiobooks ISBN.
The issue that comes up when I insert into writtenby
is that postgresql wants the ISBN I insert into writtenby
to be in both Books and Audiobooks.
It makes sense to me to have a table writtenby
that stores authors and the books/audiobooks they have written, however this does not translate to a table in postgresql.
The alternative solution I am thinking of implementing was having two new relations audiobook_writtenby
and books_writtenby
but I am not sure that is a good alternative.
Could you give me an idea of how I would implement my original idea of having a single table writtenby
referencing two different tables or how I could better design my database? Let me know if you need more information.
Best Answer
There's more than one way to do this in PostgreSQL. Personally, I prefer this way.