title: Database Design and the use of non-numeric Primary Keys
body: <p>I'm currently in the process of designing the database tables for a customer &amp; website management application. My question is in regards to the use of primary keys as functional parts of a table (and not assigning "ID" numbers to every table just because). </p>
  
  <p>For example, here are four related tables from the database so far, one of which uses the traditional primary key number, the others which use unique names as the primary key:</p>
  
  <pre><code>--
  -- website
  --
  CREATE TABLE IF NOT EXISTS `website` (
    `name` varchar(126) NOT NULL,
    `client_id` int(11) NOT NULL,
    `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP,
    `notes` text NOT NULL,
    `website_status` varchar(26) NOT NULL,
    PRIMARY KEY  (`name`),
    KEY `client_id` (`client_id`),
    KEY `website_status` (`website_status`),
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- website_status
  --
  CREATE TABLE IF NOT EXISTS `website_status` (
    `name` varchar(26) NOT NULL,
    PRIMARY KEY  (`name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  INSERT INTO `website_status` (`name`) VALUES
  ('demo'),
  ('disabled'),
  ('live'),
  ('purchased'),
  ('transfered');
  
  --
  -- client
  --
  CREATE TABLE IF NOT EXISTS `client` (
    `id` int(11) NOT NULL auto_increment,
    `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP,
    `client_status` varchar(26) NOT NULL,
    `firstname` varchar(26) NOT NULL,
    `lastname` varchar(46) NOT NULL,
    `address` varchar(78) NOT NULL,
    `city` varchar(56) NOT NULL,
    `state` varchar(2) NOT NULL,
    `zip` int(11) NOT NULL,
    `country` varchar(3) NOT NULL,
    `phone` text NOT NULL,
    `email` varchar(78) NOT NULL,
    `notes` text NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `client_status` (`client_status`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
  
  --
  -- client_status
  ---
  CREATE TABLE IF NOT EXISTS `client_status` (
    `name` varchar(26) NOT NULL,
    PRIMARY KEY  (`name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  INSERT INTO `client_status` (`name`) VALUES
  ('affiliate'),
  ('customer'),
  ('demo'),
  ('disabled'),
  ('reseller');
  </code></pre>
  
  <p>As you can see, 3 of the 4 tables use their 'name' as the primary key. I know that these will always be unique. In 2 of the cases (the *_status tables) I am basically using a dynamic replacement for ENUM, since status options could change in the future, and for the 'website' table, I know that the 'name' of the website will always be unique.</p>
  
  <p>I'm wondering if this is sound logic, getting rid of table ID's when I know the name is always going to be a unique identifier, or a recipe for disaster? I'm not a seasoned DBA so any feedback, critique, etc. would be extremely helpful.</p>
  
  <p>Thanks for taking the time to read this!</p>