CODEWITHSUNDEEP
mysqldatabase-design
Matthew Chambers
Matthew Chambers

Reputation: 877

Single Table or multiple table- normalisation

I have a question that may be simple for you to answer

I have an image table below

An image can have multiple sizes- is it better practice to have three tables

One table for image details another table that joins image_details to image_paths and another table- image_sizes that joins to image paths.

Or is it best to put all the image sizes in the image table. In effect I have one image but in different sizes. Looking for the best option for optimisation. All images will have each image sizes so there is no redundancy in the future for some images not having certain sizes. I was unsure if it was best to use another table though in case we add other sizes. However i could add another column to the image table for a new image size

Image_table

CREATE  TABLE IF NOT EXISTS `warrington_main`.`image` (  
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT ,  
  `user_id` BIGINT(20) UNSIGNED NOT NULL ,  
  `alias_title` VARCHAR(255) NOT NULL ,  
  `address_id` BIGINT(20) NULL ,  
  `geolocation_id` BIGINT(20) NULL ,  
  `camera_id` MEDIUMINT(8) NULL ,  
  `title` VARCHAR(100) NOT NULL ,  
  `description` VARCHAR(2000) NOT NULL ,  
  `main_image` VARCHAR(50) NOT NULL ,  
  `thumbnail_image` VARCHAR(50) NOT NULL ,  
  `thumbnail_image_medium` VARCHAR(50) NOT NULL ,  
  `thumbnail_image_small` VARCHAR(50) NOT NULL ,  
  `thumbnail_image_gallery` VARCHAR(50) NOT NULL ,  
  `hits` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' ,  
  `show_comment` ENUM('0','1') NOT NULL ,  
  `feature_in_gallery` ENUM('0','1') NOT NULL ,  
  `created_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' ,  
  `date_taken` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' ,  
  `updated_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' ,  
  `updated_by` BIGINT(20) UNSIGNED NOT NULL ,  
  `approved` ENUM('Inprocess','Yes','No') NOT NULL DEFAULT 'Inprocess' ,  
  `visible` ENUM('0','1') NOT NULL DEFAULT '0' ,  
  `account_type_created` ENUM('S','Y', 'G', 'FL', 'FB') NOT NULL ,  
  PRIMARY KEY (`id`) ,  
  UNIQUE INDEX `alias_title` (`alias_title` ASC) ,  
  INDEX `title` (`title` ASC) ,  
  INDEX `approved` (`approved` ASC) ,  
  INDEX `visible` (`visible` ASC) ,  
  INDEX `feature_in_gallery` (`feature_in_gallery` ASC) ,  
  INDEX `fk_image_image_user1_idx` (`user_id` ASC) ,  
  INDEX `fk_image_camera1_idx` (`camera_id` ASC) ,  
  INDEX `fk_image_address1_idx` (`address_id` ASC) ,  
  INDEX `fk_image_geolocation1_idx` (`geolocation_id` ASC) ,  
  INDEX `fk_image_user1_idx` (`updated_by` ASC) ,  
  CONSTRAINT `fk_image_image_user1`  
    FOREIGN KEY (`user_id` )  
    REFERENCES `warrington_main`.`user` (`id` )  
    ON DELETE NO ACTION  
    ON UPDATE NO ACTION,  
  CONSTRAINT `fk_image_camera1`  
    FOREIGN KEY (`camera_id` )    
    REFERENCES `warrington_main`.`camera` (`id` )  
    ON DELETE NO ACTION  
    ON UPDATE NO ACTION,  
  CONSTRAINT `fk_image_address1`  
    FOREIGN KEY (`address_id` )  
    REFERENCES `warrington_main`.`address` (`id` )
    ON DELETE NO ACTION  
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_image_geolocation1`  
    FOREIGN KEY (`geolocation_id` )  
    REFERENCES `warrington_main`.`geolocation` (`id` )  
    ON DELETE NO ACTION  
    ON UPDATE NO ACTION,  
  CONSTRAINT `fk_image_user1`    
    FOREIGN KEY (`updated_by` )    
    REFERENCES `warrington_main`.`user` (`id` )    
    ON DELETE NO ACTION    
    ON UPDATE NO ACTION)    
ENGINE = InnoDB    
AUTO_INCREMENT = 23162    
DEFAULT CHARACTER SET = utf8

Upvotes: 1

Views: 165

Answers (3)

Bharat Sinha
Bharat Sinha

Reputation: 14363

Well you should go for normalization where you use multiple tables and don't store the same information at more than one place. This redundancy avoids number of data issues later in any application life cycle.

So in your case IMAGE_SIZE should be another table with IMAGE_ID as foreign key defined.

IMAGE
    ID
    IMAGE_ATTRIBUTES

IMAGE_SIZE
    ID
    IMAGE_ID
    SIZE_ATTRIBUTES 

 IMAGE_PATH
    IMAGE_ID or IMAGE_SIZE_ID (depends)
    PATH

Upvotes: 0

Vikdor
Vikdor

Reputation: 24124

One table for image details another table that joins image_details to image_paths and another table- image_sizes that joins to image paths.

This is not normalization and is referred to as Vertical Partitioning where you want the primary details of an object to be present in one table and the auxiliary details that occupy more space (in general) and are not part of general filtering criteria are moved to a different table. This is done to reduce the size of each record of the first table so that there will be more number of records in each database page and hence less number of pages to go through during filtering, less IO and faster search ability.

In your case, it seems fine to retain all attributes of an image in a single table.

Upvotes: 1

Prescott
Prescott

Reputation: 7412

One table is probably the way to handle it in this case. If all images X have all sizes and you probably aren't likely to be adding tons of different sizes.

The three table would work just as well, but why deal with the joins when you don't really gain much.

Images
    ImageDetailsId (PK)
    ImageSizeId (PK)
    URL
    ...

Image_Details
   ImageDetailsId
   ...

Image_Sizes (where this table is relatively static - small, medium, large..)
   ImageSizeID
   ... (width? height? etc?)

Upvotes: 0

Related Questions