1

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!

Larissa Leite
  • 1,358
  • 3
  • 21
  • 36

1 Answers1

3

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

Mohsen Heydari
  • 7,256
  • 4
  • 31
  • 46