Association between primary keys and other columns Options

[Christian J]

I'm writing a CMS where a site owner can specify any arbitrary order for the DB rows to be printed on the web page (as opposed to e.g. alphanumerical sorting). There seem to be two ways to do this.

* Either you swap column values between rows so that a DB table like this (where the ID column is the primary key):

CODE

+----+--------+
| ID | name   |
+----+--------+
| 1  | Mr Foo |
+----+--------+
| 2  | Mr Bar |
+----+--------+
| 3  | Mr Baz |
+----+--------+

turns into this:

CODE

+----+--------+
| ID | name   |
+----+--------+
| 1  | Mr Bar |
+----+--------+
| 2  | Mr Foo |
+----+--------+
| 3  | Mr Baz |
+----+--------+

(after which you can order the query by the ID column when printing to a web page). Note how Mr Foo might be said to swap ID value with Mr Bar in the process. Is this a bad thing to do, provided that the ID values are just auto-incremented integers with no special meaning for the persons?

* The other idea is to add a dedicated "rank" column, and sort queries after the values there. That way the association between ID and name is retained, but it's harder to make bug free and I'm having trouble making the form controls intuitive, so I'd rather avoid it.

[Brian Chandler]

Note how Mr Foo might be said to swap ID value with Mr Bar in the process. Is this a bad thing to do, provided that the ID values are just auto-incremented integers with no special meaning for the persons?

Yes, this is a Bad thing to do. Keep things simple. If the ID is just an ID, that should be it, for ever. Apart from anything else, you will typically want other tables that refer to these people to use the ID value as a unique key.

What do you mean by "arbitrary"? That the user can manually arrange the entries in some order? If so, yes add a column called seq(uence). Then it all depends on how much how often resorting is needed.

The point-and-grunt standard is to have boxes marked "move up" and "move down", but this requires using javascript, and isn't really very usable at all (being p-a-g). Personally I just type in numeric values, typically leaving gaps to move other items into later. You could very easily have a "renumber" function that renumbers them all 10 apart -- *because* these number have no significance other than their order, you know you can do this.

(I don't see why this would be hard to make bug free though.)

HTH

[Christian J]

Yes, this is a Bad thing to do. Keep things simple. If the ID is just an ID, that should be it, for ever. Apart from anything else, you will typically want other tables that refer to these people to use the ID value as a unique key.

What if the table contains say a list of the site owner's current favorite songs? Then I guess he would tend to erase the names of previous favorites and write new song names on the same rows. Should he instead be encouraged/forced to create new rows for new favorites, while archiving the previous ones in the DB?

What do you mean by "arbitrary"? That the user can manually arrange the entries in some order?

Yes.

The point-and-grunt standard is to have boxes marked "move up" and "move down", but this requires using javascript, and isn't really very usable at all (being p-a-g).

I made something like that without JS, using a submit (or radio) button for each row. So far I haven't managed to both move rows and save changes to the text fields during the same form submission. Of course I can instruct the site owner to always save text field changes before moving rows, but I'd like to minimize such limitations.

Personally I just type in numeric values, typically leaving gaps to move other items into later. You could very easily have a "renumber" function that renumbers them all 10 apart -- *because* these number have no significance other than their order, you know you can do this.

I tried that too, but it looks pretty crude IMO, at least if you want to sell it to someone.

[Brian Chandler]

Yes, this is a Bad thing to do. Keep things simple. If the ID is just an ID, that should be it, for ever. Apart from anything else, you will typically want other tables that refer to these people to use the ID value as a unique key.

What if the table contains say a list of the site owner's current favorite songs? Then I guess he would tend to erase the names of previous favorites and write new song names on the same rows. Should he instead be encouraged/forced to create new rows for new favorites, while archiving the previous ones in the DB?

Well this is an entirely different table (or "should" be)? You need:

o Add new song
o Amend a song (e.g. correcting typos)
o Reorder songs in current preference
o Delete song

Whether you delete the record, or just mark status=X, is up to you. If the user insists on adding new songs by using "Amend" to change every column, well, it's a bit silly, but not a problem.

What do you mean by "arbitrary"? That the user can manually arrange the entries in some order?

Yes.

The point-and-grunt standard is to have boxes marked "move up" and "move down", but this requires using javascript, and isn't really very usable at all (being p-a-g).

I made something like that without JS, using a submit (or radio) button for each row. So far I haven't managed to both move rows and save changes to the text fields during the same form submission. Of course I can instruct the site owner to always save text field changes before moving rows, but I'd like to minimize such limitations.

Personally I just type in numeric values, typically leaving gaps to move other items into later. You could very easily have a "renumber" function that renumbers them all 10 apart -- *because* these number have no significance other than their order, you know you can do this.

I tried that too, but it looks pretty crude IMO, at least if you want to sell it to someone.

If there are <20 rows, then no doubt the smartest thing is a java applet/Flash gizmo/whatever, that lets you drag and drop. (I don't know if it's possible to d-a-d with javascript)

But if there are more than about 20 rows, it's not really feasible to envisage the whole thing in your head, and d-a-d isn't going to be very usable. If the person _really_ needs to be able to order arbitrarily, they will not really change everything around that much, and renumbering is simple, much faster than p&g shifting, and robust. If you provide a "renumber 10 apart" function, I do not see that intelligent customers would want to object. (Of course if you're selling to a marketing department tinsel will be better than usability.)

One other simple and robust solution is to provide a text area with the titles in order, and let the user resort it by cut-and-paste (this is what Google adwords does in various places).