Re: table with sort_key without gaps

On Mon, Dec 13, 2004 at 19:37:41 +0100,
Janning Vygen <vygen(at)gmx(dot)de> wrote:
>
> ok, i have users which wants to manage their sporting competitions which
> (simplified) has games and fixtures (in german "Spieltage", i hope the word
> fixtures is understandable). Like German "Bundesliga" has 9 games on
> "Spieltag 1", 7 on saturday and two on sunday.
>
> So i have a table:
>
> CREATE TABLE spieltage (
> account text NOT NULL,
> sort int4 NOT NULL,
> name text NOT NULL
> PRIMARY KEY (account, sort),
> UNIQUE (account, name)
> )
>
> and another table (which is not interesting here) with games having a foreign
> key referencing spieltage(account, sort). Of course every "spieltag" has a
> unique name but needs more important a sort column.
>
> I need to have sort as a primary key or at least a unique key (which is nearly
> the same) because many other tables should reference the (primary or
> candidate) key (account, sort) for the main reason that i can easily sort
> other tables according to the sort column without the need to make a join.
>
> updating/inserting/deleting to the table spieltage takes happen very seldom,
> but it should be possible.

For this emaxmple, I suggest considering using a numeric column for doing
the sorting. You can initial load it with integer values in a number of
ways. When you need to insert a new row with a value between two existing
rows you can use the fractional part of the sort value to give you an
apropiate value without having to modify existing rows.
It doesn't sound like you need to worry about renumbering after deletions,
since gaps shouldn't cause a problem in the sort order. For the actual
reports, the application can number the records consecutively as they
are returned rather than displaying the sort column values.