Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Redundant table or not?

    Hi,

    I have the following two tables:

    Code:
    CREATE TABLE `tbl_company_profiles` (
      `company_id` int(10) unsigned NOT NULL auto_increment,
      `company_name` varchar(100) NOT NULL,
      `company_type` smallint(5) unsigned NOT NULL,
      `company_addressLine1` varchar(100) NOT NULL,
      `company_addressLine2` varchar(100) default NULL,
      `company_city` varchar(50) NOT NULL,
      `company_state` varchar(50) NOT NULL,
      `country_id` int(10) unsigned NOT NULL,
      `company_zipcode` varchar(20) NOT NULL,
      `company_phone` varchar(30) NOT NULL,
      `company_fax` varchar(30) default NULL,
      `company_email` varchar(100) NOT NULL,
      `company_url` varchar(100) NOT NULL,
      `company_desc` text NOT NULL,
      `company_add_date` datetime NOT NULL,
      PRIMARY KEY  (`company_id`),
      KEY `country_id` (`country_id`),
      KEY `company_name` (`company_name`),
      CONSTRAINT `tbl_company_profiles_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `tbl_countries` (`country_id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=3 /*!40100 DEFAULT CHARSET=utf8*/;
    Code:
    CREATE TABLE `tbl_images` (
      `image_id` int(10) unsigned NOT NULL auto_increment,
      `company_id` int(10) unsigned NOT NULL,
      `image_name` varchar(50) NOT NULL,
      `image_height` tinyint(3) unsigned NOT NULL,
      `image_width` tinyint(3) unsigned NOT NULL,
      `image_type` varchar(50) NOT NULL,
      `image_ext` varchar(10) NOT NULL,
      PRIMARY KEY  (`image_id`),
      KEY `company_id` (`company_id`),
      CONSTRAINT `tbl_images_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `tbl_company_profiles` (`company_id`) ON DELETE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=2 /*!40100 DEFAULT CHARSET=utf8*/;
    Is table images a redundant table if a company can only have one image associated with it, i.e should I move the image fields to the company table? Since the image fields are mandatory fields and a company does not necessarily need to have an image associated with it, if I were to move the image fields to the company table then they would have to be re-defined as default NULL. Basically I would like to know which is best practice and good database design.

    cheers.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii
    Since the image fields are mandatory fields and a company does not necessarily need to have an image associated with it, if I were to move the image fields to the company table then they would have to be re-defined as default NULL.
    that's the right conclusion, but the logic is slightly off

    if the company does not need to have an image, then the image columns are ~not~ mandatory

    as for best practice, what do you do if a company needs to be registered but it does not have a fax number?
    Last edited by r937; 09-01-09 at 08:26.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    that's the right conclusion, but the logic is slightly off

    if the company does not need to have an image, then the image columns are ~not~ mandatory

    as for best practice, what do you do if a company needs to be registered but it does not have a fax number?
    Yes I see your point. But an image has specific attributes associated with it. In this instance if the table has an image_name then all the other image attributes must be completed. By moving the image details to company table I can not enforce that integrity. I would have to do this at a different layer programatically.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •