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

    Which is good database design?

    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.

    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 the company table I can not enforce that integrity. I would have to do this at a different layer programatically. Basically I would like to know which is best practice and good database design.

    cheers.

  2. #2
    Join Date
    Sep 2008
    Posts
    33
    I'm not very good at db design but I think they (people who actually know about db design) are going to ask you to move the address data to a separate table. :P And also to throw away the prefixing (tbl_ and company_) seeing as it's redundant. Personally I'd probably put the image in a blob if it's just a question of storing a company logo (I think that's a big "NO-NO" in db design though so you better not do it.) It's probably a good idea to separate the image from the company information if you're going to be storing all those image properties, for the sake of clarity if not for anything else.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by ozzii
    Which is good database design?
    Having two tables is the best option and you have already identified exactly the reasons why. If you bundle entities that don't belong together into one table then you'll have to use unnecessary nulls and as a result you'll make some attributes optional that shouldn't be.

    I find it's good practice to begin every design using only non-nullable tables and decomposing attributes into new tables if necessary to achieve that. Only put a nullable attribute in if you have reason to (for example if it's impossible to implement some constraint across attributes in different tables). In your case I think you've done the right thing.

Posting Permissions

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