CODEWITHSUNDEEP
mysqldatabasedatabase-designmysql-workbench
Larissa Leite
Larissa Leite

Reputation: 1368

Database design with foreign key possibly being from different tables

I am trying to design a database for my application and I am struggling with something.

I need to store a Collection:

enter image description here

As you can see, the Collection has an ID and a date, easy so far. (Please ignore the analysisID)

The problem is the target, which is a bit tricky. A Collection can have two different types of targets (let's say, a book or a magazine - mutually exclusive), both having IDs that could, eventually, coincide, as they are in two different tables. What would be a good approach for this? I thought about creating two different foreign keys (one for book and the other for magazine), and one of them would always be NULL, which I think is far from the best approach.

Sorry if something was unclear, if you have any doubts, please just ask :)

Thanks!

Upvotes: 1

Views: 148

Answers (1)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

It seems in your application Book entity and Magazine entity can take the same role, it is called target role.
It leads me to have a base table for book and magazine containing shared properties of them, like name, date-released, ... I am going to call the base table Writing table.

Writing table will have a one-to-one relationship to magazine and book (forigen key of writing will be the primary key of book and magazine).

Having the writing key in collection table (target-id) will solve the problem.

enter image description here

Upvotes: 3

Related Questions