Simple problem?

[iamgigglz]

This is a particularly annoying issue that I can't seem to find a way around. I'll probably wake up at 2am with the solution, but I figured I'd post here first.

Let's say I have a list of book pages stored in a SQL table, with an ID, page number and page contents. By selecting ordered by page number the pages will be displayed in the right order. Easy.

Now let's say I want to insert a new page 3. The old page 3 becomes page 4, old page 4 becomes page 5... Also easy.

Now let's say that I'm not allowed to rename existing page numbers when I insert a new page. How do I structure the table to allow for new pages to be inserted?
Extra columns are allowed.

I could number the pages 10, 20, 30 etc instead of 1,2,3. My new page 3 could then be given number 25 and the original page order would be maintained.
The same effect could be attained with decimals too.
Those seem like very hacky, short-sighted solutions though.

Surely there's a "right way" to do this?

 

[∀Z‾soʇɐuɐɥ┴]

Add a new column that introduces a 2nd integer value.
So lets say you have pages 1 to 5.
Integer one would be original page number like 1,2,3,4,5 etc
Integer two would be the 2nd number. All current pages would be 0.
Then say you want to insert a new page between the current page 3 and 4 you can number the new page as such
Integer one = 3 and integer 2 = 1
So your page essentially becomes 3.1, then the next page is 3.2 etc
So if you want to order order by integer 1 then integer 2.
Shout if this doesn't make sense.

 

[iamgigglz]

Makes sense (and I appreciate the response), but it's very similar to the 10,20,30 or decimals solution.
Also, what happens if I want to insert a page between 3.1 and 3.2?

It seems like a fairly basic problem but all the solutions I can think of are very limited in some way.

 

[froggeh]

When you retrieve the data from database, will you need to display the original page numbers somewhere? Just trying to wrap my head around why you wouldn't be allowed to rename the page numbers, considering one page will effectively take it's place?

Just a napkin idea, but maybe add a second pg# column which will contain the actual page number to be used for display, which is renamed when a new page is added and increment the following pages by X. The newly added page "primary" page number then becomes whichever is next in line, whereas the second is the sequence in which it's displayed. The second int could then be used for order by.

For auditing purposes you could add another table which simply holds the ID of the record(s) which were changed, what sequence number it was at the time (second int), what it changed to, and the timestamp of the change.

It's too Monday for these kinds of questions...

 

[iamgigglz]

Thanks @Flid. Too Monday indeed

No need to display the numbers. The only critical thing is that the pages appear in order.
The actual thing I'm dealing with has nothing to do with pages in a book; I'm just using the analogy to easily explain what I'm trying to do.

I'm slowly coming to terms with the fact that I'll have to run through the table and ++ all pages after the new page.

It just blows my mind that there isn't a way to do this. Imagine you have an ordered list of a billion people and you need to insert a new entry; are you supposed to run through the table updating every row with a new orderID?

 

[thePridge]

I'm guessing the pages belong to a certain book...And you know which page number the NEW page should slot in at...
I.e. you want to add page number 3.

UPDATE pages SET page = page+1 WHERE page_num >= @inserted_page_num AND book_id = @book_id

The other option is to have a created and modified date... Then order by page_num, modified_date

Just touch the pages in order that they should be displayed... but that's messier.

 

[froggeh]

Thanks @Flid. Too Monday indeed

No need to display the numbers. The only critical thing is that the pages appear in order.
The actual thing I'm dealing with has nothing to do with pages in a book; I'm just using the analogy to easily explain what I'm trying to do.

I'm slowly coming to terms with the fact that I'll have to run through the table and ++ all pages after the new page.

It just blows my mind that there isn't a way to do this. Imagine you have an ordered list of a billion people and you need to insert a new entry; are you supposed to run through the table updating every row with a new orderID?

In this case, since you don't need to display the numbers and it's purely for ordering... Maybe consider adding a 'sort order' column with a high number, based on the original. Something like page # x 10,000 + 999.

Eg data could look something like:

ID Pg Content Sort
1 1 xxxx 10999
2 2 yyyy 20999

Then if you need to insert a new page 2, you simply lookup the lowest Sort int in the 20000 range. That result - 1 will be the sort for newly inserted page, e.g:

ID Pg Content Sort
1 1 xxxx 10999
2 2 yyyy 20999
3 2 yyyy 20998

This way you'd still need to query the database for the lowest Sorting number currently in the DB, but eliminates the need to update records after it. It will however limit you to the number of "revisions" allowed per page, but this can be increased by rather using a multiplier of say 1,000,000 and adding 999,999 instead of what x10,000 + 999.

I don't think this is the cleanest method, but should be quick to implement and should give you exactly the result you're looking for.

 

[iamgigglz]

Yup @Flid, that principle of numbering the pages with huge numbers to allow gaps in between seems to be the way forward. As you say, it doesn’t seem to be the best way, but it’ll work.

 

[SantaMuerte]

@iamgigglz would you mind explaining what you are actually ordering and how your structure is set up instead of the whole book and page numbering analogy?

 

[iamgigglz]

Basically we "host" products for business clients. We're building a system to allow our clients to build forms that their clients will then complete. My list of pages is actually a list of fields on the form, and the weird limitations I'm facing come from the way were dynamically mapping these form fields back to our back end.

I've set up a meeting with the project managers and systems guys to rearrange how we're doing this. My new plan will allow for a unique field listing per client per product, so the need to auto-sort the list of fields goes out the window.

Hopefully the meeting goes to plan and my OP question becomes moot.

 

[antonbotha]

It seems like you are trying to build a dynamic form that has it's inputs loaded based on the order the form was set up and if the client adds an input at a specific place it should then be stored at the position for future loads.

If that is the case, store it as an encoded JSON in a text field in your database. This would allow you to store the fields in the order it is currently in on editor if you're using a indexed table/list.

When a form is loaded, just decode the JSON and programmatically build the form using the indexed table/list.