Déjà Vu
I have a habit of choosing projects which are tricky to implement as relational databases, which is probably why database design has become one of my least favourite topics in computer engineering. Apologies for boring the hell out of you all but I needed somewhere to write this down…
The Project
Our database design project is a database used to track personal book collections, to track the ownership of books and manage the lending of books between individuals. Simple.
Our initial E-R diagram looks like this:
The Problem
Looking at our E-R diagram the lecturer immediately pointed out that the circular relationship towards the left of the diagram was going to be problematic when implemented in Microsoft Access (yeah, I know, but we have to use the tools we're given) and we need to get rid of it.
This simplified section of the diagram shows the problem area:
I argued that this was the best solution I could come up with using normalisation. A book can have both an owner and a loanee (who are both users) and I could not find an alternative method which would not involve duplicate data.
The text book solution
In “The Relational Database” by John Carter this type of relationship is called a ternary relationship. The book states these types of relationships can always be and should always be turned into multiple binary relationships, even if this involves adding an extra entity.
The example given in the book is a lecturer who recommends text books for courses they lecture on:
My interpretation of the solution given in the text book is to replace the “recommends” relationship with a relationship type “recommends”. This relationship type is then changed into an entity type, “recommendation” because it is more natural to think of entity types having attributes than relationship types having attributes. You then replace the ternary relationship with three binary relationships, all with the new “recommendation” entity. So you end up with something like this:
I don't really understand this solution, because there seems to be no easy way to tell which courses a lecturer lectures on, having removed that link. This information can only be inferred if a lecturer has actually made a text recommendation, and may require a subquery. Sam thinks that the solution to this might be to have an entry in the recommendations table which has a lecturer, a course, but no text. I'm not sure.
The real problem?
OK, so that is possibly a solution, but I'm not sure what I can call the extra entity to make it make semantic sense. Receipt? Posession? Even then, there is an added complication…
In the text book example, a lecturer recommends a text for a course. But in our case, a user loans a book to another user. The user has two roles in the system, that of owner and loanee. I can't think of a way of solving this problem without duplicating data.
Where to look for better solutions
There is a section in the text book called “Cardinality and optionality”. I don't know what they are, should I read that section?
I'm wondering if this is a limitation of relational databases which is solved in object oriented databases.
I'm hoping there is very obvious and clean solution that I've missed and I'm just being a dumb ass!