Opinios on Database ER Schema

giannis · #1 (**permalink**) 09-29-10, 12:39

Hello Guys,

I'm new in database designing and I would like to ask your opinion about the database I've designed. Below, you can see the database schema and the MySQL code given by the MySQL Workbench.

Do you think that the database can be more optimized?

http://img842.imageshack.us/img842/7765/db1.png

Code:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`users_details`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`users_details` (
  `userID` BIGINT NULL AUTO_INCREMENT ,
  `name` VARCHAR(50) NULL ,
  `last_name` VARCHAR(50) NULL ,
  `address` BIGINT NULL ,
  `country` VARCHAR(20) NULL ,
  `telephone` DECIMAL(10,0)  NULL ,
  `birthdate` DATE NULL ,
  `url` TEXT NULL ,
  `twitter` TEXT NULL ,
  `facebook` TEXT NULL ,
  `linkedin` TEXT NULL ,
  PRIMARY KEY (`userID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`connection`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`connection` (
  `userID` BIGINT NULL AUTO_INCREMENT ,
  `contestID` BIGINT NULL ,
  PRIMARY KEY (`userID`, `contestID`) );


-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`users` (
  `userID` BIGINT NULL AUTO_INCREMENT ,
  `username` VARCHAR(50) NOT NULL ,
  `password` VARCHAR(20) NOT NULL ,
  `email` VARCHAR(100) NOT NULL ,
  `registration_date` DATETIME NOT NULL ,
  `users_details_userID` BIGINT NOT NULL ,
  `connection_userID` BIGINT NOT NULL ,
  `connection_contestID` BIGINT NOT NULL ,
  PRIMARY KEY (`userID`, `connection_userID`, `connection_contestID`) ,
  CONSTRAINT `fk_users_users_details`
    FOREIGN KEY (`users_details_userID` )
    REFERENCES `mydb`.`users_details` (`userID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_connection1`
    FOREIGN KEY (`connection_userID` , `connection_contestID` )
    REFERENCES `mydb`.`connection` (`userID` , `contestID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE INDEX `fk_users_users_details` ON `mydb`.`users` (`users_details_userID` ASC) ;

CREATE INDEX `fk_users_connection1` ON `mydb`.`users` (`connection_userID` ASC, `connection_contestID` ASC) ;


-- -----------------------------------------------------
-- Table `mydb`.`summary`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`summary` (
  `contestID` BIGINT NOT NULL AUTO_INCREMENT ,
  `organiser_name` VARCHAR(45) NOT NULL ,
  `prizeID` VARCHAR(45) NULL ,
  `timeline` DATETIME NOT NULL ,
  PRIMARY KEY (`contestID`, `organiser_name`, `prizeID`, `timeline`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`organiser`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`organiser` (
  `organiser_name` VARCHAR(45) NOT NULL ,
  `mobile` DECIMAL(10,0)  NULL ,
  `address` BIGINT NULL ,
  `url` TEXT NOT NULL ,
  `userID` BIGINT NOT NULL ,
  `users_userID` BIGINT NOT NULL ,
  `users_connection_userID` BIGINT NOT NULL ,
  `users_connection_contestID` BIGINT NOT NULL ,
  PRIMARY KEY (`organiser_name`, `userID`) ,
  CONSTRAINT `fk_organiser_users1`
    FOREIGN KEY (`users_userID` , `users_connection_userID` , `users_connection_contestID` )
    REFERENCES `mydb`.`users` (`userID` , `connection_userID` , `connection_contestID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE INDEX `fk_organiser_users1` ON `mydb`.`organiser` (`users_userID` ASC, `users_connection_userID` ASC, `users_connection_contestID` ASC) ;


-- -----------------------------------------------------
-- Table `mydb`.`timeline`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`timeline` (
  `timelineID` BIGINT NULL AUTO_INCREMENT ,
  `timezone` TIME NULL ,
  `start` DATETIME NULL ,
  `end` DATETIME NULL ,
  `entry_type` TEXT NULL ,
  PRIMARY KEY (`timelineID`) );


-- -----------------------------------------------------
-- Table `mydb`.`contest`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`contest` (
  `contestID` BIGINT NULL AUTO_INCREMENT ,
  `title` TEXT NOT NULL ,
  `summary` MEDIUMTEXT NOT NULL ,
  `details` LONGTEXT NULL ,
  `terms` TEXT NOT NULL ,
  `countries` VARCHAR(45) NOT NULL ,
  `timeline_timelineID` BIGINT NOT NULL ,
  `connection_userID` BIGINT NOT NULL ,
  `connection_contestID` BIGINT NOT NULL ,
  `summary_contestID` BIGINT NOT NULL ,
  `summary_organiser_name` VARCHAR(45) NOT NULL ,
  `summary_prizeID` VARCHAR(45) NOT NULL ,
  `summary_timeline` DATETIME NOT NULL ,
  PRIMARY KEY (`contestID`, `timeline_timelineID`, `summary_contestID`, `summary_organiser_name`, `summary_prizeID`, `summary_timeline`) ,
  CONSTRAINT `fk_contest_timeline1`
    FOREIGN KEY (`timeline_timelineID` )
    REFERENCES `mydb`.`timeline` (`timelineID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_contest_connection1`
    FOREIGN KEY (`connection_userID` , `connection_contestID` )
    REFERENCES `mydb`.`connection` (`userID` , `contestID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_contest_summary1`
    FOREIGN KEY (`summary_contestID` , `summary_organiser_name` , `summary_prizeID` , `summary_timeline` )
    REFERENCES `mydb`.`summary` (`contestID` , `organiser_name` , `prizeID` , `timeline` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE INDEX `fk_contest_timeline1` ON `mydb`.`contest` (`timeline_timelineID` ASC) ;

CREATE INDEX `fk_contest_connection1` ON `mydb`.`contest` (`connection_userID` ASC, `connection_contestID` ASC) ;

CREATE INDEX `fk_contest_summary1` ON `mydb`.`contest` (`summary_contestID` ASC, `summary_organiser_name` ASC, `summary_prizeID` ASC, `summary_timeline` ASC) ;


-- -----------------------------------------------------
-- Table `mydb`.`sponsor`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`sponsor` (
  `sponsor_name` VARCHAR(50) NOT NULL ,
  `moderation` BINARY NOT NULL ,
  `facebook` BINARY NOT NULL ,
  `prizeID` BIGINT NOT NULL ,
  PRIMARY KEY (`sponsor_name`, `prizeID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`prize`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`prize` (
  `prizeID` BIGINT NULL AUTO_INCREMENT ,
  `prize_name` VARCHAR(50) NOT NULL ,
  `prize_value` DECIMAL(10,0)  NOT NULL ,
  `prize_details` LONGTEXT NULL ,
  `prize_image` TEXT NOT NULL ,
  `contest_contestID` BIGINT NOT NULL ,
  `contest_timeline_timelineID` BIGINT NOT NULL ,
  `sponsor_sponsor_name` VARCHAR(50) NOT NULL ,
  `sponsor_prizeID` BIGINT NOT NULL ,
  PRIMARY KEY (`prizeID`, `contest_contestID`, `contest_timeline_timelineID`) ,
  CONSTRAINT `fk_prize_contest1`
    FOREIGN KEY (`contest_contestID` , `contest_timeline_timelineID` )
    REFERENCES `mydb`.`contest` (`contestID` , `timeline_timelineID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_prize_sponsor1`
    FOREIGN KEY (`sponsor_sponsor_name` , `sponsor_prizeID` )
    REFERENCES `mydb`.`sponsor` (`sponsor_name` , `prizeID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE INDEX `fk_prize_contest1` ON `mydb`.`prize` (`contest_contestID` ASC, `contest_timeline_timelineID` ASC) ;

CREATE INDEX `fk_prize_sponsor1` ON `mydb`.`prize` (`sponsor_sponsor_name` ASC, `sponsor_prizeID` ASC) ;


-- -----------------------------------------------------
-- Table `mydb`.`contest_has_organiser`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`contest_has_organiser` (
  `contest_contestID` BIGINT NOT NULL ,
  `contest_timeline_timelineID` BIGINT NOT NULL ,
  `organiser_organiser_name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`contest_contestID`, `contest_timeline_timelineID`, `organiser_organiser_name`) ,
  CONSTRAINT `fk_contest_has_organiser_contest1`
    FOREIGN KEY (`contest_contestID` , `contest_timeline_timelineID` )
    REFERENCES `mydb`.`contest` (`contestID` , `timeline_timelineID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_contest_has_organiser_organiser1`
    FOREIGN KEY (`organiser_organiser_name` )
    REFERENCES `mydb`.`organiser` (`organiser_name` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE INDEX `fk_contest_has_organiser_organiser1` ON `mydb`.`contest_has_organiser` (`organiser_organiser_name` ASC) ;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

r937 · #2 (**permalink**) 09-29-10, 13:20

i will do just one table, users_details, the others you will have to do yourself based on these comments

`userID` BIGINT NULL ,

if you want PRIMARY KEY AUTO_INCREMENT, you have to say NOT NULL

also, BIGINT is way too big, use INTEGER

`address` BIGINT NULL ,

BIGINT is wrong, use VARCHAR(255)

`telephone` DECIMAL(10,0) NULL ,

DECIMAL is wrong, use VARCHAR(20)

`url` TEXT NULL ,
`twitter` TEXT NULL ,
`facebook` TEXT NULL ,
`linkedin` TEXT NULL ,

TEXT is wrong for all of these, use VARCHAR(255) instead

finally, this should not be a separate table from the users table

giannis · #3 (**permalink**) 09-29-10, 19:12

r937 thank you for your reply and your help! I followed your instructions and here are the results:

http://img828.imageshack.us/img828/4838/dbnew.png

Better now?

healdem · #4 (**permalink**) 09-30-10, 04:37

do you repeat the same information in the same table?

I don't think you have grasped the concept of defining keys using workbench
theres two main types
identifying and non identifying
identfying means the foreign key (the bit that refers to the other table) is also this tables primary key
non-identfying means the foreign key (the bit that refers to the other table) does not comprise this tables primary key

an identifying key will usually be found in an intersection table, a non identufying key will be more common and is used in foreign keys.

Im alwasy suspicious of one to one relationships (not that they don't exist, but that in my experience they are relatively rare in the real world.

I don't understand your concept of timeline, unless you say run the same contest multiple times, but in that case you woudl want t atable for contesttypes and then a table for specific contests (which would inlcude and date/time parameters as those are attributes of a specific contest.

theres a heck oif a lot of elements comprising the primary key in contest, either your key definition is hooky, or you proabbly need to consider using an autogenerated ID for the contest table.

generally speaking I don't think its a smarrt call to make a name a Primary key, unless you are confident that no one will ever change names.. its not good in perrformance terms to change the primary key especially if it has lots and lots of references elsewhere.

the attribute countries in contest dosn't feel right, that sounds like a candidate for a countries table with an intersection table o associate sepcific countries with a specific contest
presumably if that is your intention then you will need to store the user's country as well with a FK to countries

what is the purpose of summary, is that not just pulling 4 columns from contest? if so then its an irrelevant table it can be drawn from the contest table whenever required.

contesthasorganiser is an odd table
there are 2 PK's on organiser and 3 in contest has organiser it looks to me as if you have miss defined columns comprising the key.

in table organiser I doubt you need a composite pk
either the organiser has a username /userid or deosn't personally I wouldn't use organisername as the PK, I'd use an organiserID
by all means have a userid in that tabel but not part of the PK. presumably you are saying a contest MUST have an organiser but may or may not also be a user.

I suspect you may benefit from boning up on the principles behind normalisation
Fundamentals of Relational Database Design -- r937.com
or
The Relational Data Model, Normalisation and effective Database Design
seem to be the most referred to on this site, but there are other texts out there

Internet Services

Web Development

Digital Marketing

Consumer Tech