Id Numbers

[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