Database gurus.

hargi

hargi

hargi

Associate
Joined
19 Jul 2006
Posts
1,847
Im trying to get my design right first time and came up with this on MySQL
work bench.

I have 4 tables which is all I think I need.
Reader
Tag
Uploader
Resource


Diagram

Now the reader can have many interests so this would be a one to many relationship to the Tag table?
The resource can have may tags so again this would be one to many?
The uploader can upload many resources to this is one to many also?

Is this right? Have i missed anything out?

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`.`Reader`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Reader` (
  `idReader` INT NOT NULL AUTO_INCREMENT ,
  `Username` VARCHAR(45) NOT NULL ,
  `Password` VARCHAR(20) NOT NULL ,
  `Email` VARCHAR(60) NOT NULL ,
  `Interests` VARCHAR(45) NOT NULL COMMENT 'Relates to tags. A user can have many interests\n' ,
  PRIMARY KEY (`idReader`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Tag`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Tag` (
  `idTag` INT NOT NULL AUTO_INCREMENT ,
  `Tag` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`idTag`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Resource`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Resource` (
  `idResources` INT NOT NULL AUTO_INCREMENT ,
  `Title` VARCHAR(45) NOT NULL ,
  `Dateuploaded` TIMESTAMP NOT NULL ,
  `Deleationdate` DATETIME NOT NULL ,
  `Discription` LONGTEXT NOT NULL ,
  `Link` VARCHAR(45) NOT NULL ,
  `Tags` VARCHAR(45) NOT NULL ,
  `Uploadedby` VARCHAR(45) NOT NULL ,
  `Contactdetails` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`idResources`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Uploader`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Uploader` (
  `idUploader` INT NOT NULL AUTO_INCREMENT ,
  `Username` VARCHAR(45) NOT NULL ,
  `Password` VARCHAR(45) NOT NULL ,
  `Email` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`idUploader`) )
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
 
From what I understand of your OP it looks good, however you'll need two intermediary tables to deal with the 1-to-many relationships from Reader and Resource to Tags eg:

Code: 
CREATE  TABLE IF NOT EXISTS `mydb`.`Resource_Tag` (
  `idResourceTag` INT NOT NULL AUTO_INCREMENT ,
  `idResources` INT NOT NULL ,
  `idTag` INT NOT NULL
  PRIMARY KEY (`idResourceTag`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `mydb`.`Reader_Tag` (
  `idReaderTag` INT NOT NULL AUTO_INCREMENT ,
  `idReader` INT NOT NULL ,
  `idTag` INT NOT NULL
  PRIMARY KEY (`idReaderTag`) )
ENGINE = InnoDB;
 
Thanks spunkey,

Can you refresh my memory, how do i get these intermediate tables to work
I understand that we don't want to repeat data. So in the tags table we have
id tag
1 football
2 rugby
3 running

which is fine in the resources table
id title tags
1 blah *1&2

I know we cant do this because we need the intimediate table, but i dont understand how this works.

Feeling really dumb
 
No worries, here's a quick schema of this model I've knocked up.

Code: 
Tags
idTag		Tag
-----------------------------------------------------------------
1		Football
2		Rugby
3		Running


Reader
idReader	Username	Password	Email
-----------------------------------------------------------------
1		myUser		myPassword	[email protected]
2		yourUser	yourPassword	[email protected]


Reader_Tag
idReaderTag	idReader	idTag
-----------------------------------------------------------------
1		1		1
2		1		3
3		2		2
As you can see it's the Reader_Tag table which handles the joins between the two tables. In this example Reader #1 has interests in football and running, whereas Reader #2 likes rugby.

Also, because we're using these tables you wont need the Interests field in Reader, or the Tags field in Resources.
 
So this?

New Diagram

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 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Reader`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Reader` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`Reader` (
  `idReader` INT NOT NULL AUTO_INCREMENT ,
  `Username` VARCHAR(45) NOT NULL ,
  `Password` VARCHAR(20) NOT NULL ,
  `Email` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`idReader`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Tag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Tag` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`Tag` (
  `idTag` INT NOT NULL AUTO_INCREMENT ,
  `Tag` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`idTag`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Uploader`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Uploader` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`Uploader` (
  `idUploader` INT NOT NULL AUTO_INCREMENT ,
  `Username` VARCHAR(45) NOT NULL ,
  `Password` VARCHAR(45) NOT NULL ,
  `Email` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`idUploader`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Resource`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Resource` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`Resource` (
  `idResources` INT NOT NULL AUTO_INCREMENT ,
  `Title` VARCHAR(45) NOT NULL ,
  `Dateuploaded` TIMESTAMP NOT NULL ,
  `Deleationdate` DATETIME NOT NULL ,
  `Discription` LONGTEXT NOT NULL ,
  `Link` VARCHAR(45) NOT NULL ,
  `Contactdetails` VARCHAR(45) NOT NULL ,
  `idUploader` INT NOT NULL ,
  PRIMARY KEY (`idResources`) ,
  INDEX `fk_Resource_Uploader1` (`idUploader` ASC) ,
  CONSTRAINT `fk_Resource_Uploader1`
    FOREIGN KEY (`idUploader` )
    REFERENCES `mydb`.`Uploader` (`idUploader` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Resource_Tag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Resource_Tag` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`Resource_Tag` (
  `idResourceTag` INT NOT NULL AUTO_INCREMENT ,
  `idResources` INT NOT NULL ,
  `idTag` INT NOT NULL ,
  PRIMARY KEY (`idResourceTag`) ,
  INDEX `fk_Resource_Tag_Tag1` (`idTag` ASC) ,
  INDEX `fk_Resource_Tag_Resource1` (`idResources` ASC) ,
  CONSTRAINT `fk_Resource_Tag_Tag1`
    FOREIGN KEY (`idTag` )
    REFERENCES `mydb`.`Tag` (`idTag` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Resource_Tag_Resource1`
    FOREIGN KEY (`idResources` )
    REFERENCES `mydb`.`Resource` (`idResources` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Reader_Tag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Reader_Tag` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`Reader_Tag` (
  `idReaderTag` INT NOT NULL AUTO_INCREMENT ,
  `idReader` INT NOT NULL ,
  `idTag` INT NOT NULL ,
  PRIMARY KEY (`idReaderTag`) ,
  INDEX `fk_Reader_Tag_Reader` (`idReader` ASC) ,
  INDEX `fk_Reader_Tag_Tag1` (`idTag` ASC) ,
  CONSTRAINT `fk_Reader_Tag_Reader`
    FOREIGN KEY (`idReader` )
    REFERENCES `mydb`.`Reader` (`idReader` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Reader_Tag_Tag1`
    FOREIGN KEY (`idTag` )
    REFERENCES `mydb`.`Tag` (`idTag` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
 
You must log in or register to reply here.
Back
Top Bottom