? about databases (mysql)

[Jas123]

In a table, (how can you/should you have to) manage your primary keys so that they remain continuous? For instance, if you have a table of users and your primary key is a user id, then when a user is deleted, say user 5, then your table will read 1,2,3,4,6. If this process is then multiplied, the gaps will be more dramatic.

Is this something to worry about? if so, how do you fix it?

 

[redwarrior]

I don't see the need in managing this. Why would you care if they remained continuous?

Anyway, wouldn't you want the user to just become inactive? instead of being deleted? especially if the user was responsible for any types of transactions.

If the record is deleted then yes, there will be gaps, but if there are other tables that are keyed by user ID, you would have to update them as well. That could turn into a processing nightmare.

Just a few things to consider.

 

[Jas123]

This came to mind because it doesn't seem all that prudent to have such gaps. Of course, this is in theory to me, so I asked the ? to see how it is in practice.

Maybe I used a bad example of users; so if you had some other sort of data that was added/removed frequently, maybe messages/comments, is it still wise to just ignore the gaps? If you had 1 - 1000 records, and 250 dropped out, it seems like the next 250 entries should replace those now vacant spaces.

 

[redwarrior]

Exactly. It depends on the type of data being stored and how it's going to be retrieved, presented, and maintained. (I'm not an expert on this, so don't take just my word on it.) I would prefer that data like comments or messages be indexed by date/time, which of course, would not cause the situation you are asking about.

Storage is so cheap now that data very often isn't deleted, just archived.

If you are really interested in this, perhaps take some database management classes, to learn how the physical data is stored along with the organization of indexes.

 

[Angelo95210]

It works like that. You surely do not want all your record indexes to be changed every time you delete a record. So do not worry about non continous indexes, because it works like that

 

[grapes911]

I'm assuming your primary keys are being used to uniquely identify each record and have to meaning to the end user. If so, why do you care if there are gaps or not?

Example, if you have three records and the primary keys are 123, 124, and 1234567890123456789, does it matter? Each record is still uniquely identified. That is the purpose of the primary key.