Fixing database structure. Help required.
Posted: Sat Mar 14, 2009 4:05 pm
// Table `propertys`
// Table `propertys_relations`
// Table `treasury`
// Table `propertys2` (old `propertys` table)
I used to do so: every word has it's own #id.
Each word is related with options from `propertys2` table by #id.
Now I have created two new talbes: `propertys` & `propertys_relations`, so
my main task is to transfer all records from the old to the new database structure.
That is if there were a word, let's sai 1#'car', and it had it's options:
1#hibr(0), slp(1), vert(1), ret(0)... etc.
I need to relate new propertys (`propertys_relations`) only
from positive columns from the `propertys2` table.
Result should be:
I hope it is understandable what I want.
Current propertys data:
As you can see it is in the same order as columns were in the `propertys2`table.
Code: Select all
CREATE TABLE IF NOT EXISTS `propertys` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=26 ;Code: Select all
CREATE TABLE IF NOT EXISTS `propertys_relations` (
`word` int(10) NOT NULL,
`property` int(10) NOT NULL,
UNIQUE KEY `word` (`word`,`property`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Code: Select all
CREATE TABLE IF NOT EXISTS `treasury` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`word` varchar(60) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `word` (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7350;Code: Select all
CREATE TABLE IF NOT EXISTS `propertys2` (
`id` int(10) NOT NULL,
`hibr` tinyint(1) NOT NULL DEFAULT '0',
`slp` tinyint(1) NOT NULL DEFAULT '0',
`vert` tinyint(1) NOT NULL DEFAULT '0',
`ret` tinyint(1) NOT NULL DEFAULT '0',
`snek` tinyint(1) NOT NULL DEFAULT '0',
`tarm` tinyint(1) NOT NULL DEFAULT '0',
`dzn` tinyint(1) NOT NULL DEFAULT '0',
`psn` tinyint(1) NOT NULL DEFAULT '0',
`sv` tinyint(1) NOT NULL DEFAULT '0',
`spec` tinyint(1) NOT NULL DEFAULT '0',
`prk` tinyint(1) NOT NULL DEFAULT '0',
`menk` tinyint(1) NOT NULL DEFAULT '0',
`niek` tinyint(1) NOT NULL DEFAULT '0',
`stp` tinyint(1) NOT NULL DEFAULT '0',
`knyg` tinyint(1) NOT NULL DEFAULT '0',
`plg` tinyint(1) NOT NULL DEFAULT '0',
`vaik` tinyint(1) NOT NULL DEFAULT '0',
`vulg` tinyint(1) NOT NULL DEFAULT '0',
`mom` tinyint(1) NOT NULL DEFAULT '0',
`malon` tinyint(1) NOT NULL DEFAULT '0',
`mzb` tinyint(1) NOT NULL DEFAULT '0',
`juok` tinyint(1) NOT NULL DEFAULT '0',
`iron` tinyint(1) NOT NULL DEFAULT '0',
`poet` tinyint(1) NOT NULL DEFAULT '0',
`ist` tinyint(1) NOT NULL DEFAULT '0',
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;I used to do so: every word has it's own #id.
Each word is related with options from `propertys2` table by #id.
Now I have created two new talbes: `propertys` & `propertys_relations`, so
my main task is to transfer all records from the old to the new database structure.
That is if there were a word, let's sai 1#'car', and it had it's options:
1#hibr(0), slp(1), vert(1), ret(0)... etc.
I need to relate new propertys (`propertys_relations`) only
from positive columns from the `propertys2` table.
Result should be:
Code: Select all
INSERT INTO `propertys_relations` (`word`, `property`) VALUES
(1, 2),
(1, 3);Current propertys data:
Code: Select all
INSERT INTO `propertys` (`id`, `name`) VALUES
(1, 'hibr'),
(2, 'slp'),
(3, 'vert'),
(4, 'ret'),
(5, 'snek'),
(6, 'tarm'),
(7, 'dzn'),
(8, 'psn'),
(9, 'sv'),
(10, 'spec'),
(11, 'prk'),
(12, 'menk'),
(13, 'niek'),
(14, 'stp'),
(15, 'knyg'),
(16, 'plg'),
(17, 'vaik'),
(18, 'vulg'),
(19, 'mom'),
(20, 'malon'),
(21, 'mzb'),
(22, 'juok'),
(23, 'iron'),
(24, 'poet'),
(25, 'ist');