0

I am creating an application where an Author can create a Book with say 10 Chapters, and let Readers read it. But I feel that the Author should be able to rearrange Chapters in order, sometimes DELETE a Chapter, or INSERT a new Chapter in between others.

My idea so far is to add a column to my chapter table called chapter_number and let the users add the numbers to it when creating or editing chapters, and based on that sort it when fetching it from the DB.

I am having trouble understanding how I should handle extra additions, say, an Author wants to INSERT a Chapter with chapter_number as 4, and expect the rest of the rows to automatically sort themselves. I understand that the way to handle this is to have chapter_number saved in multiples of 10 or 100 so that we have a 'room' between the rows.

But it feels a bit hacky to me. Is there a better way?

1
  • I refactored the answer to address the question as actually asked - HTH!
    – Vérace
    Jun 8, 2020 at 11:59

1 Answer 1

1

Edit:

The main thrust of my first answer (now at the bottom of this post) did not properly address the question which was about:

how I should handle extra additions, say, an Author wants to INSERT a Chapter with chapter_number as 4, and expect the rest of the rows to automatically sort themselves.

and not how to design a (flexible) schema for the storing of book and chapter data.

I'll leave the schema design stuff in place under a line, just in case anyone is interested in the future and deal with the core issue in this edit.

You'll have to ensure that your chapters remain in the correct sequence programmatically, either using TRIGGERs or through your coding environment of choice or perhaps a combination of both? You mentioned in comments that you use Django - Python is a very powerful tool and should be well capable of tackling any programming task that is thrown at it - programmer competence being a given.

My own personal view is that the database is the last bastion of defence of your data and that any data manipulation/treatment/processing should be done as close to the server as possible. Since you apparently haven't settled on an RDBMS yet, it's impossible to give any definitive code that would help resolve the problem.

All I will say is that PostgreSQL's PL/pgSQL is vastly more powerful than MySQL's procedural language extension (see here and here - a quote from the second reference: It closely resembles Oracle's PL/SQL language. - although admittedly not as sophisticated). À vous le choix!

Do as much of the work as you can server-side using it - I would give some thought as to how using the WITH RECURSIVE construct might be of help. Googling the term PostgreSQL recursive query produces many helpful pages! A good example of its power can be seen in an answer to a recent question here.

Finally, I couldn't let this go without my pet rant (was in first answer - moved to here)!

<getting_on_to_white_charger>

I'm firmly of the belief that those who produce database systems are remiss and lackadaisical in their implementation of more features from within DDL.

If rules are stored in the structure of the database itself, then no matter how stupid, inexperienced or just plain lazy your contract programmer, student intern or half-wit cousin that your wife insisted you give a job to is, they still can't mess up the system!

The vastly underrated Firebird RDBMS is one of only two systems) that support CHECK constraints with subqueries! A small glimpse into the power of this feature can be seen in this example (from yours truly!).

</getting_on_to_white_charger>

Phwew! Deep breath - relieved that I got that off my chest! :-).


Initial answer (schema design - didn't address the question):

I understand that the way to handle this is to have chapter_number saved in multiples of 10 or 100 so that we have a 'room' between the rows.

This is one way of tackling the problem - however, see below!

But it feels a bit hacky to me. Is there a better way?

You are correct, it is desperately "hacky" and it's great that you are looking for a more elegant solution.

Such a solution exists in the form of joining or linking tables. These are more formally called "Associative Entities" and are really helpful when there is (and/or may be) an arbitrary number of connections between one entity (i.e. table) and another. (There are actually 17 alternative names for this construct mentioned on the wiki page).

I did something like this many moons ago, so this is from memory, but I think that you should be able to adapt it for your needs. There is a fiddle here. The example is PostgreSQL, but it works for every server on the dbfiddle.uk palette. The reason for this is these two lines in the chapter_author table:

ca_is_primary_author CHAR(1),

and

CONSTRAINT ca_is_primary_author_ny CHECK (ca_is_primary_author IN ('Y', 'N'))

I used this construct because neither Oracle nor SQL Server have a BOOLEAN type and I wanted to keep the example as generic as possible. You may well want to make use of features of your own particular RDBMS when you come to apply any solution?

My idea of the tables' DDL would look something like this:

author table:

-- 1000 is very long for a name, but there could be a corporate author?
-- For PostgreSQL you could just use a TEXT field (1GB) or for MySQL you could
-- use MEDIUMTEXT (16MB). I used VARCHAR here because it will work for all servers,
-- but you may want to make use of types provided by your RDBMS of choice. Oracle,
-- and SQL Server undoubtedly have their own equivalents

CREATE TABLE author 
(
  author_id INTEGER NOT NULL PRIMARY KEY,
  author_name VARCHAR (1000) NOT NULL, -- 1000 is long but maybe corporate?
  author_contact VARCHAR (30) NOT NULL -- mobile number?
  --
  -- other fields
  --
);

work table:

-- could be called "book", "tome", "volume" - whatever
-- note that there is no "author" field - many works are multi-author 
-- and/or compendiums or the results of conferences/symposia/whatever
-- even different chapters may have different authors - see below    

CREATE TABLE work 
(
  work_id INTEGER NOT NULL PRIMARY KEY,
  work_title VARCHAR (100) NOT NULL,
  work_publisher INTEGER NOT NULL, -- could point to a publisher table?
  work_subject VARCHAR (1000) NOT NULL
  --
  -- other fields
  --
);

chapter table:

-- There is no "chapter_author" **_FIELD_**.
-- This is because even one chapter may be written by more than one author - this 
-- is especially common for academic works where a "chapter" might well be
-- a scientific article in a journal!
-- This situation is covered in the "chapter_author" **_TABLE_**!

-- There is no "chapter_work" **_FIELD_**.
-- This is because a given chapter may appear in more than one work - a compendium 
-- for example like Michael Stonebraker's "Readings in Database Systems"
-- https://www.thriftbooks.com/w/readings-in-database-systems-mogan-kaufmann-series-in-data-management-systems_michael-stonebraker/1147410/#isbn=1558605231&idiq=5917200
-- which is a compilation of articles previously published elsewhere
-- This situation is covered  in the "work_chapter" **_TABLE_**!

CREATE TABLE chapter
(
  chapter_id INTEGER NOT NULL PRIMARY KEY,
  chapter_number INTEGER NOT NULL,
  chapter_title VARCHAR (100) NOT NULL,
  chapter_subject VARCHAR (1000)  -- abstract of chapter?
  -- 
  -- other fields
  --
);

chapter_author table:

This is our first Associative Entity - it (obviously enough!) links chapters to authors!

CREATE TABLE chapter_author
(
  ca_work_id INTEGER NOT NULL,
  ca_author_id INTEgER NOT NULL,
  ca_is_primary_author CHAR(1),
  CONSTRAINT chapter_author_pk PRIMARY KEY (ca_work_id, ca_author_id),
  CONSTRAINT ca_work_fk FOREIGN KEY (ca_work_id) REFERENCES work (work_id),
  CONSTRAINT ca_author_fk FOREIGN KEY (ca_author_id) REFERENCES author (author_id),
  CONSTRAINT ca_is_primary_author_ny CHECK (ca_is_primary_author IN ('Y', 'N'))
);

work_chapter table:

-- Having the three fields as a PRIMARY KEY means that you can't have the same
-- chapter in the same position in the same work. You may need a mechanism to ensure
-- that there are no gaps in the sequence of chapters as you INSERT, UPDATE and DELETE?

CREATE TABLE work_chapter
(
  wc_work_id INTEGER NOT NULL,
  wc_chapter_id INTEGER NOT NULL,
  wc_chapter_position INTEGER NOT NULL,
  CONSTRAINT work_chapter_pk PRIMARY KEY (wc_work_id, wc_chapter_id, wc_chapter_position),
  CONSTRAINT wc_work_fk FOREIGN KEY (wc_work_id) REFERENCES work (work_id),
  CONSTRAINT wc_chapter_fk FOREIGN KEY (wc_chapter_id) REFERENCES chapter (chapter_id)
);

As I mention in the table definition, you will need to have some mechanism to enforce a "no gaps" rule in the sequence of the chapters - unfortunately, DDL isn't (yet) sophisticated enough to be able to enforce such complex rules.

As an aside:

p.s. welcome to the forum!

7
  • Thank you. My database model is very simple though. I have a Book table. And a Chapter table with book_id as a ForeignKey. The Chapter table has a column named chapter_number and a UNIQUE together constraint on (book_id, chapter_number). This way I am thinking I can have (1, 1) (1, 2) (1, 3) and at the same time (2, 1) (2, 2) (2, 3) (can't have the same chapter in the same position in the same work). So my question is more about the mechanism of how to retain the order when inserting a new (1, 2) without the need to rewrite the old (1, 2) to (1, 3) and so on.
    – Ken
    Jun 7, 2020 at 14:03
  • This would have to be done through (possibly) TRIGGERs and/or your programming language of choice (how are you accessing the database programmatically?). As I mentioned in my "white charger" purple patch, DDL isn't very powerful and certainly not powerful enough to implement a rule of this scope. It really depends on your RDBMS - PostgreSQL, Oracle and MS SQL Server have procedural extensions that are sophisticated enough to deal with this scenario - I don't think that MySQL's language would be up to the job!
    – Vérace
    Jun 7, 2020 at 14:14
  • Ah, thanks. It's clarified now. So you're saying that the implementation should be based on the application layer (in my case Django). Or if I choose the database layer, I should use TRIGGER. Got it. So in that case, the core logic is still to rewrite all rows right? If I have to insert a chapter at position 2, I'll have to rename the chapter_number for the consecutive rows? This seems so taxing on the DB, but it seems like this is the only way it can be done. And thanks again for the answer, I got a pretty good idea on multiple authors and multiple chapters.
    – Ken
    Jun 7, 2020 at 14:19
  • What's your RDBMS? You might be able to get some mileage out of recursive CTE's? All the major servers have them nowadays. In theory, with them, SQL is Turing complete, so, again in theory, any programming task should be feasible with SQL - however, just because you can drive your car off a cliff doesn't mean that you should. Choose the tool for the job - Django might be just the job (I'm a fan of Python!).
    – Vérace
    Jun 7, 2020 at 15:10
  • 1
    My final word: if you haven't started your project yet - go for PostgreSQL - it's vastly superior to MySQL both in terms of features (though I will grant that MySQL has made strides recently) and in terms of standards compliance. You won't regret it!
    – Vérace
    Jun 7, 2020 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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