gistfile1.sql

-- EXAMPLE QUERY FOR USER'S primary address:
SELECT A.address1, A.address2, A.city, A.state, A.zip FROM users U
LEFT JOIN users_addresses UA ON U.id = A.user
LEFT JOIN address A ON UA.address = A.id
WHERE UA.primary = 1 AND UA.deleted = 0;

-- ADDRESS
CREATE TABLE IF NOT EXISTS `addresses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address1` varchar(225) NOT NULL,
  `address2` varchar(225) NOT NULL,
  `city` varchar(225) NOT NULL,
  `state` varchar(225) NOT NULL,
  `zip` varchar(225) NOT NULL,
  `country` int(11) NOT NULL,
  `createdat` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

-- User Address Relation Table
CREATE TABLE IF NOT EXISTS `users_addresses`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` int(11) NOT NULL,
  `user` int(11) NOT NULL,
  `primary` tinyint NOT NULL DEFAULT 1,
  `deleted` tinyint NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY (`user`)
)

-- USER
CREATE TABLE IF NOT EXISTS `users` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `ip_address` varbinary(16) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(40) NOT NULL,
  `salt` varchar(40) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `activation_code` varchar(40) DEFAULT NULL,
  `forgotten_password_code` varchar(40) DEFAULT NULL,
  `forgotten_password_time` int(11) unsigned DEFAULT NULL,
  `remember_code` varchar(40) DEFAULT NULL,
  `created_on` int(11) unsigned NOT NULL,
  `last_login` int(11) unsigned DEFAULT NULL,
  `last_activity` int(11) NOT NULL,
  `active` tinyint(1) unsigned DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `company` varchar(100) DEFAULT NULL,
  `phone` int(11) DEFAULT NULL,
  `phone_active` tinyint(4) NOT NULL DEFAULT '0',
  `phone_activation_code` varchar(225) DEFAULT NULL,
  `frozen` tinyint(4) NOT NULL DEFAULT '0',
  `language` enum('en','de','fr','es','kr','ch') NOT NULL DEFAULT 'en',
  PRIMARY KEY (`id`),
  KEY `username` (`username`),
  KEY `email` (`email`),
  KEY `frozen` (`frozen`)
);