6

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:

  1. Restructuring our main table to get away from the Id/ParentId setup, or,
  2. 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.

1
  • 1
    I had to deal with similar needs, and always kept ParentID of root items NULL - this is a bit more efficient than where ParentID = ID. – Astrogator May 25 at 4:21
6

I would always store the intermediate entities, possibly by adding missing ones as dummy entries, so that you can query using always the same query.

E.g., store

Book > Chapter

as

Book Series (dummy) > Book > Volume (dummy) > Chapter

Now, you can query

SELECT * 
FROM
   BookSeries bs
   LEFT JOIN Book b      ON bs.BookSeriesID = b.BookSeriesID 
   LEFT JOIN Volume v    ON b.BookID = v.BookID
   LEFT JOIN Chapter c   ON v.VolumeID = c.VolumeID

where BookSeries and Volume will yield NULL columns, except for primary and foreign keys.

So, always store starting with BookSeries and end at the desired level. So If you want to store a book with no volume and no chapter, this is fine. The query will yield NULL columns for volumes and chapters (inlcuding for primary and foreign keys). This is ensured by the LEFT JOIN.

Journals have a different structure. Here two approaches are possible:

  1. Store journals as independent hierarchy in other tables.
  2. (partially) unify the two hierarchies and use a Type or Kind field as a discriminator to differentiate between books and journals
    BookSeries > BookOrJournal > Volume +--> Issue > Article
                                        |
                                        \--> Chapter
    
5

I think the suggestion from Olivier is a good one. Note that SQL Server has a special datatype for handling hierarchies called hierarchyid. From the information you have given I don't see anything which would preclude you from using this datatype.

you can read more at :- https://docs.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver15

There is also lots of discussion on the web about how to store hierarchies and a variety of different techniques.

You could start with this article which contains some of the classic references

https://www.red-gate.com/simple-talk/sql/performance/the-performance-of-traversing-a-sql-hierarchy/

3
  • 1
    I have used hierarchyid in one project, and while it was probably the best choice, it requires peculiar and hard to understand SQL queries, and I think it's a less well-known solution. Could still be worth it, though. – Christian Davén May 25 at 11:57
  • 1
    I implemented a version of this many moons ago - as you can see it only requires basic SQL sqlteam.com/articles/more-trees-hierarchies-in-sql – Stephen Morris - Mo64 May 25 at 12:12
  • 1
    The code in your example doesn't use hierarchyid at all, but implements Path Enumeration, which is another pattern that works well for this kind of data. The OP can get more ideas from here: databasestar.com/hierarchical-data-sql – Christian Davén May 25 at 12:29
1

There are hundreds (if not thousands) of other factors that can come into play in designing a data model that's right for the precise need. You've not included enough info to make a proper assessment of the best data model, but I've made some assumptions below.

The suggestions above are solid, but wanted to suggest one more common setup I've seen in the wild for this type of scenario. Heirarchical data types aren't always best. In this case, the "records" aren't of the same type (in my opinion) like you'd see in, for example, an "employees" table that links to itself to implement the employee-manager scenario.

I've understood correctly, I think we can make the following assumptions:

  • The levels (level names, quantity, and depth) don't change at all, or change rarely
  • Your lowest level of grain is a "piece of content" (chapter or article), regardless the higher-level things those individual pieces of content are part of.
  • A "piece of content" also seems to also be the level at which you sell and track subscriptions to content (perhaps it may sell to the customer as a "book" or "book series", but underneath, what they're buying is access to each individual "piece of content").
  • All higher-level entities are simply aggregations of 1 or more "piece(s) of content". They are simply ways of organizing each "piece of content" e.g. chapters make a up a book, chapters make up a book volume, chapters make up a book series, etc.

So, I might have the main entity as, say, content - which would store these blocks of content and their related attributes, and include FK columns to each higher-level categorization entity like book, book_series, etc. I can even tell which "books" are part of a given "book series" using just this single table (plus the "books" table if I need the book names instead of just a list of book_id values)

Then you can join from there to related dimensions like volume, book, book series, etc. as needed.

Beside this, you can have a cross-walk table, perhaps subscribed_content - listing which content each subscriber can access. If a subscriber buys access to a book series, they're buying access to each individual chapter in each individual book.

So when a user subscribes to, say, a book series, the application simply looks up all of the individual content pieces which make up that book series (which it can do using just a single table - or two), and then does an insert into the subscribed_content table:

insert into subscribed_content 
(
    content_id, 
    subscriber_id
)
select 
    c.ID content_id, 
    s.ID SUBSCRIBER_ID 
from 
    content c join 
    subscribers s on c.subscriber_id = s.id 
where 
    c.book_series_id = X

Thinking through a few of the common tasks you've mentioned:

Get all book series' by author X

In the content table, you'll have FK cols for book_series_id, book_id, volumn_id, journal_id, issue_id,

select  
    * 
from 
    content c join 
    book_series s on c.book_series_id = s.id
where 
    c.author_id = 1234

Find content that a subscriber can access:

select distinct 
    book_series_id -- list of book series this user can view
from 
    subscribed_content sc join 
    content c on sc.content_id = c.id
where 
    c.author_id = 1234

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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