first real db design

djemmers · #1 (**permalink**) 09-14-09, 04:07

Hi

I want to optimize a database I have. I expect a growth of the database comming year so I'd better optimize now.
I also want to make an online search engine for it but that is another matter (though it is important information as the db will be mainly used by that engine)

here is what I got to now:

Code:

CREATE TABLE IF NOT EXISTS `event` (
  `event_id` int(11) NOT NULL AUTO_INCREMENT,
  `sport_id` int(11) DEFAULT NULL,
  `title` text NOT NULL,
  `description` text NOT NULL,
  `level` text,
  `start` date NOT NULL,
  `end` date NOT NULL,
  `countrycode` varchar(2) NOT NULL,
  `provnum` tinyint(4) DEFAULT NULL,
  `city` text,
  PRIMARY KEY (`event_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;
ALTER TABLE event ADD FULLTEXT(title,description,level);

CREATE TABLE IF NOT EXISTS `Countries` (
  `countrycode` varchar(2) NOT NULL,
  `country` varchar(30) NOT NULL,
  `lang` varchar(2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `Provinces` (
  `countrycode` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `province` varchar(75) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `provnum` int(11) NOT NULL,
  `lang` varchar(2) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `Sports` (
  `sport_id` int(11) NOT NULL,
  `sport` varchar(25) NOT NULL,
  `lang` varchar(2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

where sport_id countrycode and provnum are "linked" with the other tables. In those tables are the names of spotrs/country/provincies
this to be able to load that data in dropdown boxes and the like in my search engine form. Also this takes care of misspelling, and might enable mulit language use.
What I am wondering about: Do those three last need an extra id field as primary key ? to be able to link it with the event table as foreign keys?

Maybe best a small thought about the search engine form.
My searchform will have the following field.

dropdown ->sport
textveld ->title, description
date ->date before
date ->date after
checkbox ->countries
dropdown ->if only 1 country is checked a dropdown with provinces becomes visible
dorpdown ->provinces
text ->city
text ->level

So a user that fills in the form will have roughfly the next querry

Code:

SELECT * FROM tblevent
    WHERE MATCH(title, descirpion,level) AGAINST ('search words from text title,description')
    AND sportid='dropdown sport'
    AND startdate>'date after' AND enddatum<'date before'
    AND (countrycode='country1' or countrycode='country2' or...)
    AND provinces=' dropdown provinces'
    AND stad like '%text city%'
    AND MATCH(title, description, level) AGAINST ('search words from level')

I expect there are lots of improvements to be made as I am not used to design databases.
I can code php, html, css and mysql querries though.
All tips are welcome.

djemmers · #2 (**permalink**) 10-08-09, 04:24

after a bit of tinkering I have this.(without the fulltext index this time)
any thoughts?

Code:

CREATE TABLE category (
  category_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  lang_code VARCHAR(2) NOT NULL,
  name TEXT NOT NULL,
  parent_id INTEGER UNSIGNED NULL,
  PRIMARY KEY(category_id, lang_code)
);

CREATE TABLE country (
  countrycode VARCHAR(2) NOT NULL AUTO_INCREMENT,
  lang_code VARCHAR(2) NOT NULL,
  name TEXT NOT NULL,
  PRIMARY KEY(countrycode, lang_code)
);

CREATE TABLE language (
  language_code VARCHAR(2) NOT NULL AUTO_INCREMENT,
  name VARCHAR(2) NULL,
  language VARCHAR(2) NULL,
  PRIMARY KEY(language_code)
);

CREATE TABLE provincie (
  provincie_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  lang_code VARCHAR(2) NOT NULL,
  country_lang_code VARCHAR(2) NOT NULL,
  country_countrycode VARCHAR(2) NOT NULL,
  name VARCHAR(75) NULL,
  PRIMARY KEY(provincie_id, lang_code),
  INDEX provincie_FKIndex1(country_countrycode, country_lang_code)
);

CREATE TABLE toernooi (
  toernooi_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  lang_code VARCHAR(2) NOT NULL,
  country_lang_code VARCHAR(2) NOT NULL,
  provincie_lang_code VARCHAR(2) NOT NULL,
  category_lang_code VARCHAR(2) NOT NULL,
  user_2_user_id INTEGER UNSIGNED NOT NULL,
  category_id INTEGER UNSIGNED NOT NULL,
  country_countrycode VARCHAR(2) NOT NULL,
  provincie_id INTEGER UNSIGNED NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  level_age TEXT NULL,
  startdate DATE NOT NULL,
  enddate DATE NOT NULL,
  city TEXT NOT NULL,
  street VARCHAR(75) NULL,
  number VARCHAR(10) NULL,
  postcode VARCHAR(10) NULL,
  organisator VARCHAR(75) NOT NULL,
  website VARCHAR(225) NULL,
  contact_name VARCHAR(75) NULL,
  contact_tel VARCHAR(75) NULL,
  contact_email VARCHAR(75) NULL,
  PRIMARY KEY(toernooi_id, lang_code),
  INDEX toernooi_country(country_countrycode, country_lang_code),
  INDEX toernooi_category(category_id, category_lang_code),
  INDEX toernooi_owner(user_2_user_id),
  INDEX toernooi_province(provincie_id, provincie_lang_code)
);

CREATE TABLE user_2 (
  user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name INTEGER UNSIGNED NOT NULL,
  pasw INTEGER UNSIGNED NOT NULL,
  email INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(user_id)
);

I added the 2 designs in xml DBdesigner4 format (change to xml)

Internet Services

Web Development

Digital Marketing

Consumer Tech