Table structure for adjacency model 1

[Sleidia]

Hi,

I've created a page based on the adjacency model with the following table structure :

CREATE TABLE def_pages (
  file_ID bigint(20) NOT NULL auto_increment,
  file_parent_ID bigint(20) NOT NULL default '0',
  file_lang tinytext NOT NULL,
  file_name tinytext NOT NULL,
  file_path text NOT NULL,
  file_status set('show','hide') NOT NULL default 'hide',
  UNIQUE KEY file_ID (file_ID)
) TYPE=MyISAM;

The problem is that now, for some reason I can't exlain here, I want to have an index that starts from zero and that isn't auto_increment.

So, I changed the table like this :

CREATE TABLE def_pages (
  file_ID bigint(20) NOT NULL default '0',
  file_parent_ID bigint(20) NOT NULL default '0',
  file_lang tinytext NOT NULL,
  file_name tinytext NOT NULL,
  file_path text NOT NULL,
  file_status set('show','hide') NOT NULL default 'hide',
  UNIQUE KEY file_ID (file_ID)
) TYPE=MyISAM;

The result is that my page is broken as a direct result from this change.

So, my question is : does the adjacency model absolutely require an auto_increment index starting from 1 or is it more likely to be a problem with my code?

I'm asking this because my code looks OK ... and I keep scratching my head ....

 

[Sleidia]

Well, I've tested with the query below on both table structures and the result is that the second structure outputs 3 times the number of rows issued by the first structure.

Why is that???

SELECT 
level0.file_ID AS level0_ID, level0.file_parent_ID AS level0_parent_ID, level0.file_name AS level0_name, 
level1.file_ID AS level1_ID, level1.file_parent_ID AS level1_parent_ID, level1.file_name AS level1_name, 
level2.file_ID AS level2_ID, level2.file_parent_ID AS level2_parent_ID, level2.file_name AS level2_name, 
level3.file_ID AS level3_ID, level3.file_parent_ID AS level3_parent_ID, level3.file_name AS level3_name 
FROM def_pages AS level0
LEFT OUTER JOIN def_pages AS level1 ON level1.file_parent_ID = level0.file_ID
LEFT OUTER JOIN def_pages AS level2 ON level2.file_parent_ID = level1.file_ID
LEFT OUTER JOIN def_pages AS level3 ON level3.file_parent_ID = level2.file_ID
WHERE level0.file_parent_ID = 0
AND level0.file_lang LIKE 'fr'
ORDER BY level0_name, level1_name, level2_name, level3_name
LIMIT 0 , 100

 

[r937]

um, could you explain what you're trying to do?

there's nothing wrong with using an auto_increment as the primary key

r937.com | rudy.ca

 

[TonyGroves]

You certainly don't need to use an auto-increment field. If the data in both tables is the same (have you checked that?) then the query should produce identical results.

 

[Sleidia]

Hi Tony

The only difference between tables is the index that starts from 0 instead of 1 in the second one.

Here is the comparison

Table A

CREATE TABLE `table_A` (
  `cat_ID` smallint(6) NOT NULL auto_increment,
  `cat_parent_ID` smallint(6) NOT NULL default '0',
  `cat_name` tinytext NOT NULL,
  UNIQUE KEY `cat_ID` (`cat_ID`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;

cat_ID  cat_parent_ID  	 cat_name
1 	0 	 	 	1
2 	0 	 	 	3
3 	0 	 	 	2
4 	3 	 	 	21
5 	4 	 	 	211

SELECT
level0.cat_ID AS level0_ID, level0.cat_parent_ID AS level0_parent_ID, level0.cat_name AS level0_name,
level1.cat_ID AS level1_ID, level1.cat_parent_ID AS level1_parent_ID, level1.cat_name AS level1_name,
level2.cat_ID AS level2_ID, level2.cat_parent_ID AS level2_parent_ID, level2.cat_name AS level2_name,
level3.cat_ID AS level3_ID, level3.cat_parent_ID AS level3_parent_ID, level3.cat_name AS level3_name
FROM table_A AS level0
LEFT OUTER JOIN table_A AS level1 ON level1.cat_parent_ID = level0.cat_ID
LEFT OUTER JOIN table_A AS level2 ON level2.cat_parent_ID = level1.cat_ID
LEFT OUTER JOIN table_A AS level3 ON level3.cat_parent_ID = level2.cat_ID
WHERE level0.cat_parent_ID = 0
ORDER BY level0_name, level1_name, level2_name, level3_name
LIMIT 0 , 100

=> will output 3 rows (OK)

---

CREATE TABLE `table_B` (
  `cat_ID` bigint(20) NOT NULL default '0',
  `cat_parent_ID` bigint(20) NOT NULL default '0',
  `cat_name` tinytext NOT NULL,
  UNIQUE KEY `cat_ID` (`cat_ID`)
) TYPE=MyISAM;

cat_ID  cat_parent_ID  	 cat_name
0 	0 	 	 	1
1 	0 	 	 	3
2 	0 	 	 	2
3 	2 	 	 	21
4 	3 	 	 	211

SELECT
level0.cat_ID AS level0_ID, level0.cat_parent_ID AS level0_parent_ID, level0.cat_name AS level0_name,
level1.cat_ID AS level1_ID, level1.cat_parent_ID AS level1_parent_ID, level1.cat_name AS level1_name,
level2.cat_ID AS level2_ID, level2.cat_parent_ID AS level2_parent_ID, level2.cat_name AS level2_name,
level3.cat_ID AS level3_ID, level3.cat_parent_ID AS level3_parent_ID, level3.cat_name AS level3_name
FROM table_B AS level0
LEFT OUTER JOIN table_B AS level1 ON level1.cat_parent_ID = level0.cat_ID
LEFT OUTER JOIN table_B AS level2 ON level2.cat_parent_ID = level1.cat_ID
LEFT OUTER JOIN table_B AS level3 ON level3.cat_parent_ID = level2.cat_ID
WHERE level0.cat_parent_ID = 0
ORDER BY level0_name, level1_name, level2_name, level3_name
LIMIT 0 , 100

=> will output 9 rows (NOT OK)

 

[TonyGroves]

Trouble with table B as it stands is the confusion cause by a cat_parent_id value of 0. Does it mean "no parent", or "the parent is the record with cat_id 0"? You would be better off using NULL to represent "no parent".

 

[Sleidia]

Oh... I didn't notice that !
I'll try what you advised.
Thanks