Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm looking for a better way to deal with the design I have here, I'm not confident it's really suitable.

To give this some context, lets say we have a bunch of groups, one group could be "SQL", and another could be "Cooking".

The SQL group would have books such as "Normalization" and "T-SQL", while cooking could have "Making pasta" and "Pizza is awesome".

The books themselves will have many pages, which may not end up being set in stone, I need to be able to change them around (and this is really a problem with my already devised scheme).

A table of contents for Pizza is Awesome might look like this:

1. History
    1. The great pizza revolution of 13901AD
    1. The first pizza
2. Making pizzas
    1. Mental preparation
        1. Think like a gazel
        1. Hunger like a lion
    1. Building the oven
        1. Real pizza is made with flamethrower.
            1. How to make a flamethrower
                1. How to get a permit for your flamethrower
3. Enjoying pizza
4. Credit

Then I run off and fill up my SQL tables like so:

Groups: pizza, SQL Books: Pizza is awesome Pages: All the 13 pages I listed in the example Table of contents.

Here's the snag: Table of contents.

Under my existing design, I'd have a table that gives each item it's own position, so "credits", "history", and other first-level items are assigned their position with an nested value of 0. Items like "Hunger of a lion" are in position 7 in the table of contents, with a nested value of 2 (because it's 2 levels deep).

This doesn't really provide for a bunch of needs without complex programming:

  1. What if I wanted to move making pizzas above history? I have to re-index the entire thing!
  2. What if I wanted to nest making pizzas under enjoying pizzas?
  3. What if I write a whole new section?
  4. How do I ensure nested levels make sense?

I basically have to update the entire index, and the "nested" level has no way to check if something makes sense (ie, the first item could be nested 6 times).

I'm looking for a better answer.

As a side note, the actual database includes revision changes of each page, and a place for extra content such as comments.

enter image description here

share|improve this question

migrated from codereview.stackexchange.com Jul 22 '11 at 13:37

This question came from our site for peer programmer code reviews.

add comment

2 Answers

up vote 3 down vote accepted

A number of recommendations and notes.:
1) You have to list an order of the pages somewhere (I would not rely on anything but a specific 'order' column for this) - This can either be accomplished with a 'page_number' type column (like position_in_TOC would seem to imply), or a linked-list type equivalent ('next page has this id').
2) There is a huge possibility of needing multiple groups per book; I would recommend a book_group correlation table, allowing you to have books that deal with things like ORM framworks or program database access (with groups like 'Java', 'SQL', 'C#', 'Hibernate', etc.).
3) Instead of having a 'nested factor', consider using a recursive parent-child relationship (which is precisely what the relationship is). This will also make changing which section is nested where much easier.
4) No matter what you do, you're going to have some sort of 'complex' programming. You're just going to have to deal with it.

I think a slightly more flexible design is closer to this (note: this has not been completely normalized, but should be an okay starting place):

Table Design

This allows for several things:
1) Books can be part of multiple groups, or none.
2) Sections are specifically listed as children of other sections (null if they are the top). This allows sections to be moved to be inside a different parent by simply reassigning parent_section.
3) Pages and sections both have an ordinal index. This allows them to be reordered inside their containing section, without regard for their parent's index; Page 1 is page 1 of that section, not of the entire structure, eliminating the need to re-order the entire tree if only one section changes.

There are a number of things that could be done to muck with this design, maybe adding section-heading pages or something.

share|improve this answer
2  
A linked list within a relational database sounds a bit dodgy to me. It's basically a data structure within a data structure, in a system that isn't designed to work that way. For example, delete a record without remembering to maintain the links for that list and you have a serious update anomoly - the head of the list leads into nowhere (or into some record you add later that just happens to recycle that old ID), and the tail of that list is left in limbo. That said, an order column has potential issues too, so maybe there's no perfect solution within the relational model. –  Steve314 Jul 22 '11 at 14:10
    
@Steve314: My thoughts, very well worded. –  Falcon Jul 22 '11 at 14:25
1  
I think the way to solve this is to lose the notion of numbering things altogether. You define a ranking for each level of your hierarchy, but never display it. You always generate your table of contents dynamically, ordering everything by its rank within its group. If you must, you can assign numbers to everything when you display it, based on the element's ordinal position within its group. I'd assign ranking values as multiples of ten or one hundred, to leave plenty of room for inserting new records. –  TMN Jul 22 '11 at 15:25
    
@TMN - But how do you give each record a particular ordinal position within its group, within the relational model? One relational database principle is that you're not meant to rely on the physical ordering of records within a table - only on the key-based ordering within indexes. The physical ordering within a table may not be what you expect (e.g. it may depend on the DBMS strategy for allocating new records in a file that contains deleted record slots), and may change unexpectedly (e.g. during a deleted-record-slot cleanup). Some explicit ordering mechanism seems necessary. –  Steve314 Jul 22 '11 at 15:39
    
@TMN - Yes, my thinking about how index is used is along these lines. I know the design isn't perfect, but I figured this would be a better starting place. –  Clockwork-Muse Jul 22 '11 at 15:47
show 3 more comments

In my humble opinon you should pay attention to the data's true form. And the core-structure of yours isn't relational and tabular in its nature.

I don't think a relational database really suits your business domain naturally. A database to store hierarchical data, like an XML-database for example (eXist), might suit your business domain better, as it offers more freedom in storing and retrieving hierarchical data. eXist also integrates some features like FullText-Search nicely (Lucene integration).

You can consider a hybrid-approach, storing just a part of your data as XML in a relational database with XML features (Oracle comes to mind).

Anyway, extracting the hierarchies can be really difficult on some RDBMS, although some can retrieve such data in a single query, Oracle with it's CONNECT BY statement for example. And mofifying such hierarchies can be cumbersome, too.

share|improve this answer
    
It's an interesting solution. I'm using postgres which I believe has OO design and may be suitable to do this too. –  Incognito Jul 22 '11 at 14:24
    
I don't know much about Postgres, and maybe Postgres is the way to go, but that's another story. You should ask that as a separate question. I think Postgres still locks you into a relational model, just with objects. You need a hierarchical data store. Please correct me if I am wrong. –  Falcon Jul 22 '11 at 14:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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