We've been browsing for a while looking for an answer to this, but any of them suited exactly my needs, I hope is a relevant question.

We have a database for managing books. In this database, a same person can be author and editor, or author and translator.

We have chosen this schema:

enter image description here

However, wen we design the view for books, that have to have the title, the author, the translator, etc., we have to do it like this, in order to get the name of the person related to the role:

enter image description here

But we know that it's not the best way. I think is a relevant question, because this problem is a very common one in order to design a good and solid database, and this knowledge is not always specified in manuals.

Thank you!

  • 1
    I think your schema is pretty good. What I don't get from the question is what exactly the problem is. Sep 3, 2014 at 8:47
  • Thank you! It's because in order to do the view, we think that maybe dupolicate or tirplicate peronsa and idioma is not a good solution, and that maybe the schema lacks some consistency.
    – Ramon
    Sep 3, 2014 at 8:50
  • With "to do the view", do you mean that the query (or view) has to reference some tables 2 or 3 times? Sep 3, 2014 at 8:51
  • Yes, sorry, that's exact!
    – Ramon
    Sep 3, 2014 at 8:52
  • 2
    Then that is fine, I see no problem. Queries that reference a table more than once are pretty common. What could be improved is the autor (and the other 2 tables (traductor and editor.) The id_autor and persona_id_persona do not need to be 2 columns. One column (say id_autor) can be both the primary key and a foreign key to persona. Sep 3, 2014 at 8:55

1 Answer 1


Just to help other people with same doubts: the good answer is ypercube's commentary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.