Id Numbers

[anti]

I want to ask a few things about row id; the numbers in the first column of tables which auto-increment.

Many website CMS packages use these numbers in the url when serving content.

Eg. http://www.mydomain.com/news.php?5

Which serves the data in the 5th row of the 'news' database table.

Now, my favourite CMS (e107) supports multi-language content.

It does this by having two tables.

Eg. 'english_news' and 'spanish_news'

When the language selection is changed, the system switches to using the corresponding DB tables. Whilst not perfect, it is probably the only way multi-language could have been introduced without breaking all existing sites. As long as the two tables' id numbers are kept sycronised, there is no problem. For instance, when I create an english news item, I have to remember to also switch language and create the same item in spanish. This is easy if one person is submitting content.

Where it falls down is when users are able to submit content (Eg. on a school website, several different teachers will want to create news items). It is much more likely then, that people will forget to create the spanish version – the tables will quickly become out of sync. This means if I am readin an English item about a sports day, when I change language the item will not correspond with the one I've just been reading – it will be completely unrelated.

There's nothing I can do about that really – I can't stop it from happening.

However, once the tables are out of sync, I could edit the id numbers to bring them back into sync.

Eg.

[1] news1 [1] noticias1

[2] news2 [2] noticias2

[3] news3 [3] noticias4

[4] news4 [4] noticias5

[5] news5 [5] noticias6

[6] news6

You can see that someone forgot to create a spanish version of news3, and another few items were posted before the problem was noticed. What I'd like to do is increment the spanish id numbers 3,4,5 all at once and then simply insert 3. However, no editor I've tried will let me do that. The process is:

increment 5 to 6

increment 4 to 5

increment 3 to 4

create a news item in id7

edit 7 to 3

This is a very simple example. Sometimes I have try to edit 30-40 id numbers which are very out of sync, and it is a nightmare 'shifting them around' because no two id numbers can exist at the same time, even if it is only temporarily – I have to change the id numbers one at a time.

Is there any easy way to edit and order the id numbers in a table?

Thanks.

[peterlaursen]

I think that if it is an autoincrement column (and that also is a PK), it is very different actually. The idea with using a an autoincrement column is as this 'give me a unique number and as long as it is unique I really do not care what it is'!

Such columns are inserted by the server and the user/admin is not supposed to bother about the values …

Our own FAQ (powered by phpMyFAQ) is multilingual too, but here the design is that the PK is defined on TWO columns: the 'id' column and the 'lang' column. When a 'semidublicate' (same topic – another language) is created the application explicitly writes the 'id' as identical to the same post in the other language(s) – if it is a completely new topic the the application inserts (max(id) + 1) for the new id. I think this is a good design for multilangualism.

The table definition for the main table in this system reads:

CREATE TABLE `faq_faqdata` (

`id` int(11) NOT NULL default '0',

`lang` varchar(5) NOT NULL default '',

`solution_id` int(11) NOT NULL default '0',

`revision_id` int(11) NOT NULL default '0',

`active` char(3) NOT NULL default '',

`keywords` text,

`thema` text NOT NULL,

`content` longtext,

`author` varchar(255) NOT NULL default '',

`email` varchar(255) NOT NULL default '',

`comment` enum('y','n') NOT NULL default 'y',

…

…

PRIMARY KEY (`id`,`lang`),

FULLTEXT KEY `keywords` (`keywords`,`thema`,`content`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

[anti]

I know that they (e107) shouldn't be using the id of the rows, but that's the way they developed the system from very early on in the project's management, and so most of the scripts use the row id to reference the content. I don't think this could easily be changed to the system you describe above without breaking thousands of existing websites. They may attempt it in the future, but my main concern is just to be able to manage the situation as it stands now.

So can I assume that there is no way around the 'there must never be any duplicate id numbers' rule?

I can't disable that somehow to allow me to freely edit the id numbers SQLYog?

One idea I had was to export the problematic tables to Excel, change all the id numbers there, and import back again.

Would something like that work? Would Excel be likely to corrupt the data or utf-8 characterset?

Thanks for any further advice.

[Author]

Posts