I work for a publisher, and our products are mainly books and journals. They are most commonly structured as follows:
Book > Chapter Book Series > Book > Chapter Book > Volume > Chapter Book Series > Book > Volume > Chapter Journal > Volume > Issue > Article Journal > Volume > Article
We currently have all of these records stored in the same table with Id and ParentId columns. For example, a book with TitleId = 1 that has 3 chapters would have the following rows:
Book: Id = 1, ParentId = 1 Chapter #1: Id = 2, ParentId = 1 Chapter #2: Id = 3, ParentId = 1 Chapter #3: Id = 4, ParentId = 1
All these records, whether they're books, chapters, journals, articles, etc., can have their Id connected to other tables for things like authors, prices, ownership, etc.
The problem this structure creates for us is that the nesting adds tons of overhead in certain situations. For example, if someone tries to access a journal article they purchased, we need to run multiple queries to know if they do, in fact, have access. We have an ownership table containing Id's of owned products, so we not only need to check if the user owns the journal article's Id directly, but we also need to check for ownership of the Id's for the article's parent issue, volume, and journal. (I.e., if the user owns the entire journal, it's implied they own all of the articles within that journal despite not having explicit access.) So our main "ownership" query is rather bulky since we need it to check for ownership at all possible levels of nesting.
There are many other similar situations. For example, if we need to get all the authors associated with a book, we need to run a query for the book directly, then walk down to each chapter and get all the authors for each one, or if the book has volumes, we need to walk down each volume, and get the authors for each volume's chapters.
Another situation is searching, where we need to basically aggregate all these different types to be seen as relatively equal so a search can be performed on them, and the results, whether they're a book, chapter, journal, articles, etc., all need to appear alongside each other in the search results.
What I'm wondering is if there's a way to improve our setup so we can get relational data faster (things like ownership checks, authors, prices, etc.), by either:
- Restructuring our main table to get away from the Id/ParentId setup, or,
- Building new tables/views off to the side.
I'm interested in any comments/advice/recommendations anyone has about our current setup, or proposing a new setup.