I have three tables: Books, Sagas and Universes. Books has idSaga as foreign key. Sagas has idUniverse as foreign key.

All of them are one to Many relationships.

A book can be an installment of a saga and that saga part of a bigger universe; for example: the lord of the rings and the fellowship of the ring is the first installment of the LOTR saga, and the LOTR is in the Legendarium universe.

How should i address a book like The hobbit? it isn't part of any saga but is part of the Legendarium universe.

I don't want to add a new field to the table Books (for the foreign key), it looks like breaking normalization. Adding a new table and relationships between books and universe looks a bit excesive.

CodePudding user response：

I don't want to add a new field to the table Books (for the foreign key), it looks like breaking normalization.

This is exactly what I would do. I'm not sure what you mean by "it looks like breaking normalization", but to me this seems ideal because the Book -> Universe relationship could be useful.

For example, let's say you had a new feature where you wanted to count all the books in a given universe. If you don't have a foreign key in the books table, you'd have to query the sagas table for information that is in no way related to sagas.

By excluding this foreign key, you are limiting your options for using this relationship in the future.

In summary, I would go:

Books M:1 Saga
Books M:1 Universe
Saga M:1 Universe

I also agree with the other answer that there might be cases where a book could be in multiple universes, but it's up to you to decide if that's allowed or not :)

CodePudding user response：

This breaks some form of logic as proposed if a book is “not part of a Saga” — or else both Book and Saga would need to imply Universe, yet a Book could imply a different Universe(s) than the Saga it is part of.

This is core issue is addressed if every Book is part of a Saga, even if only a Saga of the single book (The Hobbit is the only Book in The Hobbit Saga.. currently ;-)).

In the base form this is then the following, as currently described, which prevents the possibility for such contradictions and allows queries to be written consistently¹, regardless of how many Books there are in a Saga.

Books M:1 Saga
Saga M:1 Universe

Another reason to use this model, of every book being in a Saga, is it also works for first-of-Saga books when such has not yet been established (and perhaps subsequent books have not even been planned).

Of course this might be insufficiently flexible (read: too simple) with crossovers such as some comic books, depending on the desired refinement classification.. perhaps it is possible that not all Books in a Saga share the same Universe. And if such oddities are possible, then a Book needs a non-Saga relationship to Universe.

¹ While query complexity can be handled relatively simply with a view, avoiding contradictions adds more complexity to the model. One method would be to require a Book to be part of a Saga or a Universe; not both.