Re: noobie question

On Thu, Jan 24, 2013 at 12:03 PM, Steve Clark <sclark(at)netwolves(dot)com> wrote:
> On 01/24/2013 01:06 PM, Chris Angelico wrote:
>>
>> On Fri, Jan 25, 2013 at 4:45 AM, Steve Clark <sclark(at)netwolves(dot)com> wrote:
>>>
>>> Thanks All,
>>>
>>> This is for a few very small tables, less 100 records each, that a user
>>> can
>>> delete and insert records into based on the "id"
>>> which is displayed in a php generated html screen. The tables are rarely
>>> updated and when they are updated only one person
>>> is accessing them at a time.
>>>
>>> I have seen several answers on inserting what about deleting?
>>
>> Deleting works exactly the same way; you just subtract instead of adding.
>>
>> And thanks Jeff, I forgot about that requirement. Still, searched
>> update is the easiest solution.
>>
>> However, do seriously rethink your design. At very least, the "id"
>> field is misnamed; it's not the record's identity if it changes. If
>> your only two operations are "insert" and "delete" (with inserts
>> permitted at either end of the list as well as in the middle), one way
>> you could do it is to have a serially-numbered ID, and a 'pos'. Adding
>> to the end means inserting a row with a pos one higher than the
>> current highest. Inserting a record before another one means inserting
>> a row with the same pos - no renumbering needed. Deleting a row is
>> done by its id, not its position. And when you query the table, just
>> ask for them "ORDER BY POS, ID DESC" - this will show them in the
>> right order. This doesn't, however, handle arbitrary reordering of
>> records. For that, you will ultimately need to renumber the positions.
>>
>> ChrisA
>>
>>
> Hi Chris,
>
>
> It is really called rule_num and relates to "in what order firewall rules
> are applied". And it used
> to allow the user to place the firewall rules where they want them in
> relation to other rules.
>
> This is an old design, of which I had no input, but am now maintaining. Like
> I said initially I have
> php, bash or C code to do the reordering and was just wondering if there was
> a slick way to
> do it without having to resort to some external mechanism.
>
> Thanks to all who responded.

So do the numbers need to be a gapless sequence? if not why not have
each position be, say, 10,000 apart, and just insert new ones halfway
between the two nearest rules?