Anyway, I'm using innodb for the bulk of the tables as I want the db to maintain referrential integrity for me. Now the only problem here is that my search
algorithm is going to generate a "LIKE" type query statement based on the input it gets from the user. To that end, I'd love to create a FULLTEXT index.
Oh crap! Can't use FULLTEXT indexes on Inno tables! LOL
So here are my considerations on how to do this. Just wanted to get some feedback from others on this.
Option 1:
Code: Select all
CREATE TABLE `descriptions_1` (
`description_id` int(11) NOT NULL auto_increment,
`gate_id` int(11) NOT NULL,
`manifold_id` int(11) NOT NULL,
`turbo_id` int(11) NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`description_id`),
UNIQUE KEY `gate_id` (`gate_id`,`manifold_id`,`turbo_id`),
FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
to leave 2 of the 3 fields empty. However, that's still going to represent space in the db and therefore the file system. Seems a bit of a waste if you ask me. At
least the parent id's are named correctly.
Option 2:
Code: Select all
CREATE TABLE `descriptions_2` (
`description_id` int(11) NOT NULL auto_increment,
`parent_id` int(11) NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`description_id`),
UNIQUE KEY `parent_id` (`parent_id`),
FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Option 3:
Code: Select all
CREATE TABLE `descriptions_3` (
`description_id` int(11) NOT NULL auto_increment,
`parent_type` varchar(1) NOT NULL,
`parent_id` int(11) NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`description_id`),
UNIQUE KEY `parent_id` (`parent_id`),
KEY `parent_type` (`parent_type`),
FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Of course, to a lot of us, the answer may seem obvious, but perhaps there is something I'm not considering here right? There are some sharp chaps around here so it's good to get some feedback.
And the more I think about it, the second option above will not work. The parent_id can't be unique as it can potentially reference one of three seperate tables that each has their own ID generation via auto_increment.
Shouldn't MySQL have a global ID generation mechanism?
Anyway, I'm going with #3 for now unless someone can give me some good feedback as to why I shouldn't.
Cheers