help in relating tables and normailsation
Posted by:
ruud kaka
Date: May 27, 2010 08:04AM
hello, i have created my database tables in mySQL but i have problems in creating relations between tables and normalization......help will be appreciated.
here are my tables
-- Database: `db`
--
-- --------------------------------------------------------
--
-- Table structure for table `dqg_categories`
--
CREATE TABLE IF NOT EXISTS `dqg_categories` (
`ID` tinyint(3) unsigned NOT NULL auto_increment,
`Module` varchar(50) default NULL,
`Name` char(50) default NULL,
`Child` tinyint(3) unsigned default '0',
`Parent` tinyint(3) unsigned default '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
--
-- Dumping data for table `dqg_categories`
--
INSERT INTO `dqg_categories` (`ID`, `Module`, `Name`, `Child`, `Parent`) VALUES
(1, 'News', 'UKH', 0, 0),
(2, 'Downloads', 'All Files', 0, 0),
(3, 'News', 'ACIT Department', 0, 0),
(4, 'News', 'IR Department', 0, 0),
(5, 'News', 'Business Managment', 0, 0),
(6, 'Downloads', 'ukh gallery', 0, 0),
(8, 'Login', 'test', 1, 7);
-- --------------------------------------------------------
--
-- Table structure for table `dqg_downloads`
--
CREATE TABLE IF NOT EXISTS `dqg_downloads` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Cat_ID` tinyint(3) unsigned default NULL,
`Title` varchar(50) default NULL,
`Added_By` varchar(50) default NULL,
`Description` text,
`Filename` varchar(100) default NULL,
`Filename_on_disk` varchar(25) default NULL,
`Date_Added` varchar(50) default NULL,
`Downloads` int(3) unsigned default NULL,
`Permission` tinyint(3) unsigned default NULL,
PRIMARY KEY (`ID`),
KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `dqg_downloads`
--
INSERT INTO `dqg_downloads` (`ID`, `Cat_ID`, `Title`, `Added_By`, `Description`, `Filename`, `Filename_on_disk`, `Date_Added`, `Downloads`, `Permission`) VALUES
(1, 6, 'test pic', 'Admin', 'test', 'modulemanage.png', 'file-9cfabc3ced2300ba', '2010 Apr 30', 1, 1);
-- --------------------------------------------------------
--
-- Table structure for table `dqg_general`
--
CREATE TABLE IF NOT EXISTS `dqg_general` (
`Site_Title` varchar(100) default NULL,
`Site_Caption` varchar(50) default NULL,
`Default_Template` int(20) default NULL,
`Default_Language` int(20) default NULL,
`Home_Module` varchar(20) default NULL,
`Copyright` varchar(100) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `dqg_general`
--
INSERT INTO `dqg_general` (`Site_Title`, `Site_Caption`, `Default_Template`, `Default_Language`, `Home_Module`, `Copyright`) VALUES
('UKH IT 301 project- Developed by: Rudy Kaka', 'University of Kurdistan Hawler', 1, 1, 'News', 'Copyright © by rudy kaka');
-- --------------------------------------------------------
--
-- Table structure for table `dqg_groups`
--
CREATE TABLE IF NOT EXISTS `dqg_groups` (
`ID` tinyint(3) unsigned NOT NULL auto_increment,
`Name` varchar(50) default NULL,
`Permission` tinyint(3) unsigned default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `dqg_groups`
--
INSERT INTO `dqg_groups` (`ID`, `Name`, `Permission`) VALUES
(1, 'Anyone', 0),
(2, 'Registered Users', 1),
(3, 'Administrators', 10);
-- --------------------------------------------------------
--
-- Table structure for table `dqg_languages`
--
CREATE TABLE IF NOT EXISTS `dqg_languages` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Name` char(50) default NULL,
`Path` char(50) default NULL,
PRIMARY KEY (`ID`),
KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `dqg_languages`
--
INSERT INTO `dqg_languages` (`ID`, `Name`, `Path`) VALUES
(1, 'English', 'English'),
(2, 'Kurdish', 'Kurdish');
-- --------------------------------------------------------
--
-- Table structure for table `dqg_modules`
--
CREATE TABLE IF NOT EXISTS `dqg_modules` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Name` varchar(30) default NULL,
`Active` tinyint(1) unsigned default NULL,
`Permission` tinyint(3) unsigned default NULL,
`IsHomeModule` tinyint(1) unsigned default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
--
-- Dumping data for table `dqg_modules`
--
INSERT INTO `dqg_modules` (`ID`, `Name`, `Active`, `Permission`, `IsHomeModule`) VALUES
(1, 'News', 1, 0, 1),
(2, 'Admin', 1, 10, 0),
(3, 'Login', 1, 0, 0),
(4, 'Register', 1, 0, 0),
(5, 'User_Settings', 1, 1, 0),
(6, 'Members', 1, 1, 0),
(7, 'Search', 1, 0, 0),
(8, 'Categories', 1, 0, 0),
(9, 'Downloads', 1, 0, 0);
-- --------------------------------------------------------
--
-- Table structure for table `dqg_module_settings`
--
CREATE TABLE IF NOT EXISTS `dqg_module_settings` (
`Module` varchar(50) default NULL,
`Setting` varchar(50) default NULL,
`Value` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `dqg_module_settings`
--
INSERT INTO `dqg_module_settings` (`Module`, `Setting`, `Value`) VALUES
('News', 'Max_Post_in_Preview', '10'),
('Register', 'allow_registration', '1'),
('Login', 'UseSecurityCode', '0');
-- --------------------------------------------------------
--
-- Table structure for table `dqg_news`
--
CREATE TABLE IF NOT EXISTS `dqg_news` (
`Post_ID` int(10) unsigned NOT NULL auto_increment,
`Cat_ID` tinyint(3) unsigned default NULL,
`Post_Title` varchar(200) default NULL,
`Post_Author` varchar(50) default NULL,
`Post_Date` varchar(50) default NULL,
`Post_Views` int(10) unsigned default NULL,
`Post_Pre` text,
`Post_Full` text,
`Post_Permission` tinyint(3) unsigned default NULL,
PRIMARY KEY (`Post_ID`),
KEY `ID` (`Post_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Table structure for table `dqg_templates`
--
CREATE TABLE IF NOT EXISTS `dqg_templates` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Name` char(50) default NULL,
`Path` char(50) default NULL,
PRIMARY KEY (`ID`),
KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `dqg_templates`
--
INSERT INTO `dqg_templates` (`ID`, `Name`, `Path`) VALUES
(1, 'Default', 'default');
-- --------------------------------------------------------
--
-- Table structure for table `dqg_users`
--
CREATE TABLE IF NOT EXISTS `dqg_users` (
`ID` int(4) unsigned NOT NULL auto_increment,
`Username` varchar(30) default NULL,
`Password` varchar(32) default NULL,
`Email` varchar(50) default NULL,
`Register_Date` varchar(50) default NULL,
`Group` tinyint(3) unsigned default '1',
KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `dqg_users`
--
INSERT INTO `dqg_users` (`ID`, `Username`, `Password`, `Email`, `Register_Date`, `Group`) VALUES
(1, 'Admin', '21232f297a57a5a743894a0e4a801fc3', NULL, '2010/04/18', 10);