Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2001
    Posts
    45

    Question Unanswered: Moving (Displaying) Categories with Entries in a single form

    Another interesting problem:

    If have a table containing text entries with a category and a position number, like this:

    [intPosition] [intCategory] [intText]
    1 CAT1 Text1
    2 CAT1 Text2
    3 CAT1 Text3
    4 CAT2 Text4
    5 CAT2 Text5
    6 CAT2 Text6
    7 CAT3 Text7
    ... and so on....

    Now I want to provide a form that lists all categories (DISTINCT ROW) and allows the user with an arrow up/down button to move (switch) the categories in their order. Which means the whole bunch of entries that belong to that category must be switched.

    I fail in displaying these categories (I don't need to show the text-entries at this point). SELECT DISTINCT ROW [strCategory] ..... gets me all the categories, but I'm missing the [intPosition] field. But when I include the [intPosition] of course DISTINCT ROW doesnt apply to [strCategory] only any more.... Is this solveable in SQL?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is this solveable in sql? no

    it's solvable in redesigning the table(s)

    category sequence should be stored in the category table

    create table category
    ( id integer
    , catname varchar(50)
    , seq integer
    )

    then your text entry table uses the category's id as a foreign key, such that an item is linked to the category it belongs to, and you can change the sequence of the categories without touching the items

    furthermore, to produce the list of categories, you select (distinct not required) from the category table

    make sense?

    rudy
    http://r937.com/

  3. #3
    Join Date
    Nov 2001
    Posts
    45
    Thanks r937....

    this is your last word? There is no way to get around the categories table?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's put it this way

    you can manipulate the categories/items in a single table if you want

    it's messy and difficult

    or you can normalize your data and have drop-dead simple queries to do the re-sequencing, display, and maintenance of your data

    your choice


    rudy

  5. #5
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    waldemar

    I'd be normalising the data as suggested by r937. I do database maintenance work, built by other ... and I have seen some databases that are not normalised and I have written some pretty "sad, indepth, embedded" queries to finally get the data out ....



    Originally posted by r937
    let's put it this way

    you can manipulate the categories/items in a single table if you want

    it's messy and difficult

    or you can normalize your data and have drop-dead simple queries to do the re-sequencing, display, and maintenance of your data

    your choice


    rudy
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  6. #6
    Join Date
    Nov 2001
    Posts
    45
    Well I was having a longer discussion with a professional database engineer who gave me interesting insights about "over-normalizing" a database... In this case, this category-form would be indeed the only time I would need the normalisation...

    To be honest I was hoping there would be an elegant SQL solution to this issue... (maybe it's possible, to somehow join the same table to itself and include the intPosition that I need); but I guess I have to re-think this whole subject...

    thanks for help guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •