dcsimg
Results 1 to 7 of 7

Thread: Help with index of a table

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2010
    Posts
    21

    Smile Help with index of a table

    hi guys,

    the thing is i am creating a program in vb.net that connects to an informix database and then you can add registers through vb.net to de db

    everything works well but the problem starts when I want to delete a register from there i have a button that deletes the selected register from a datagrid and the button deletes it correctly

    but the index of the table doesn´t get deleted

    for example: I have a table with this 3 fields:

    ID | name | last name

    I create a register and its id is 1
    then i create another one and its id is 2
    but after that i delete both registers
    and after that i create another register that is supposed to be id = 1 cause
    the table is empty again
    but it appear as 3 keeping the id count

    any help with this?

    thanks anyway


  2. #2
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: Help with index of a table

    Yes, that's how its supposed to work... do you really need it to go back to 1? What's the basis of this requirement other than aesthetics? Was it imposed by auditor? Why can't you use 3 as the starting value? Did you check if this (reuse of unique ID) will impact your foreign key references?

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Apr 2010
    Posts
    21

    Re: Help with index of a table

    i want it that way because in the datagrid i have in the form it shows

    ID | Name | Last name (and so when you add register is appears like this)

    1 John wayne
    2 mark suers
    ( here you delete both)

    here you add a new one to this empty list ad appear like this:

    ID | Name | Last name

    3 Steven johnson

    being this the only register on the list.
    i want it always to start from 1 even when you delete all the data and add new one.

    And the reference thing there is no problem cause the db contain only 1 table

  4. #4
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: Help with index of a table

    It's a unique ID/primary key of a record, it's not a list order number. It's supposed to work that way. You're only opening a can of worms with your requirement. It's not worth the headache. Your time is better spent elsewhere, e.g. improving other areas of your application, making it work.

    Rather than making it revert to one, you should be asking whether user requires a natural key (something like a social security number) or a surrogate key (auto-increment number); google the difference. Besides, in a real system with hundreds/thousands of records, users won't give a damn whether first record started at 1 as long as the unique ID fulfills its purpose. Serializing transaction reference numbers matter more in the bigger scheme of things.
    Last edited by leinad31; Sep 2nd, 2010 at 02:59 AM.

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,538

    Re: Help with index of a table

    As Leinad has said, the purpose of an index column is to gove a unique number to every record that is ever created. So each number will be used once and once only, regardless of deletions in the system. It's not the tool you're looking for.

    Before we can provide a solution you really need to analyse your requirement further. If you create records 1, 2 and 3, then delete record 2, should record 3 now become record 2? If not your records will be 1 and 3 (with a gap), should the next record created be 2 or 4?

    If your requirement is purely aestetic (i.e. a count of displayed records) then I would suggest you shouldn't store it in the database. Instead generate the count as you query or as you populate your grid. If your requirement is about identifying individual records within your database then, as Leinad has said, you really don't want to be re-using ids, it will lead to all sorts of conistency errors.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Apr 2010
    Posts
    21

    Re: Help with index of a table

    well thanks for the advices so now i think that what i was looking for is just a variable that list them in order..

    And this program i´m making is just a one form vb.net proyect with a db cause i´m practicing my skills with it because in 2 months i will be doing a really big one and so i wanna be the more familiarized posible with it you know

    but as i said b4 thanks for your advices...

  7. #7
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: Help with index of a table

    Interacting with database is often straightforward... use a recordset (or something comparable), execute directly on connection, or do a parameterized query.

    Often the problem is with how said objects are used; excessive locking, lack of concurrency control (these are some of the problems with your revert to 1 scenario), inefficient queries, too large resultsets, etc. Developers are often content on just getting the output with no concern for scalability, maintainability and performance of their queries.

    If you have time, it would be worthwhile to read up on SQL tuning for your target database. That way you won't take all database programming samples as the only way of interacting with database as they are often poorly written (performance wise) for the sake of understandability.

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width