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!
