Fixing database structure. Help required.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Fixing database structure. Help required.

Post by spamyboy »

// Table `propertys`

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 ;
// Table `propertys_relations`

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;
// Table `treasury`

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;
// Table `propertys2` (old `propertys` table)

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);
I hope it is understandable what I want.
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');
As you can see it is in the same order as columns were in the `propertys2`table.
User avatar
spamyboy
Forum Contributor
Posts: 266
Joined: Sun Nov 06, 2005 11:29 am
Location: Lithuania, vilnius

Re: Fixing database structure. Help required.

Post by spamyboy »

Post Reply