Post reply

Schema Help

  • September 11, 2003 at 11:01 pm

    #101663

    My dillema is this:

    You have a book, it has one or many chapters, and chapters have one to many sections, but any section can only appear once in a chapter.

    Now, what would be the most efficient way to build a schema that would allow the following:

    1. The chapter order can be changed by moving a single chapter out of its current postion/order forwards or backwards (unless of course it is first or last). Once this chapter is moved to its new postion, the subsequent chapters increment their postion/order by one and their referenced sections are moved with them.

    2. A section can, likewise, move to a new position/order, also moving the following sections within that new or existing chapter by one, but also decrementing the sections from the former chapter (if applicable) by one.

    This one has me stumped.

  • September 11, 2003 at 11:42 pm

    #473858

    Groovy,

    Maybe, to enforce your rules, a schema such as:

     

    CREATE TABLE Book (

        ID INT IDENTITY PRIMARY KEY,

        Book_name VARCHAR(100))

    GO

    CREATE TABLE Chapter (

        ID INT IDENTITY PRIMARY KEY,

        BookID INT FOREIGN KEY REFERENCES Book (ID),

        ChapterNum INT NOT NULL,

        ChapterName VARCHAR(100),

        UNIQUE (BookID, ChapterNum))

    GO

    CREATE TABLE [Section] (

        ID INT IDENTITY PRIMARY KEY,

        ChapterID INT FOREIGN KEY REFERENCES Chapter (ID),

        SectionNum INT NOT NULL,

        SectionName VARCHAR(100),

        UNIQUE (ChapterID, SectionNum))

    Relationship between SECTION and CHAPTER is so that a swapping around of Chapter Numbers will NOT mean SECTIONs have to be changed too.

    Implement update/insert triggers on CHAPTER: if there's an existing chapter with the same number then increment chapter number for all chapters >= to it (starting from the highest of course).

    Same sort of trigger for SECTION.

    Cheers,

    - Mark

    Edited by - mccork on 09/12/2003 12:01:57 AM


    Cheers,
    - Mark

  • September 13, 2003 at 2:36 pm

    #474069

    Thanks,

    I orginally had two schemas, one similar to this, another with a denormalized table that had id and order columns for both chapters and sections. Triggered to update based on insert or update.

    Thank you again for the help.

  • September 14, 2003 at 6:08 pm

    #474086

    Just as a side note to mccork's table definitions, you might want to replace the fields called ID with more descriptive fieldnames. Having worked with a database where ID is used in every table, it can get very confusing to known exactly which ID you are referring to. It also means that your field definitions are the same throughout the database. eg: BookID in the Book table is the same as BookID in the Chapter table.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply