CREATE TABLE `user` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `first_name` varchar(20) NOT NULL,
      `surname` varchar(100) NOT NULL,
      `email` varchar(45) NOT NULL,
      `password` varchar(45) NOT NULL,
      `id_user_address` int(11) unsigned DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `email_UNIQUE` (`email`),
      KEY `u_ua_to_ua_id_idx` (`id_user_address`),
      CONSTRAINT `u_ua_to_ua_id` FOREIGN KEY (`id_user_address`) REFERENCES `user_address` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COMMENT='an user can be a tenant and a landlord also';

    CREATE TABLE `user_address` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `id_country` int(11) unsigned NOT NULL,
      `id_state` int(11) unsigned NOT NULL,
      `id_city` int(11) unsigned NOT NULL,
      `street` varchar(200) NOT NULL,
      `number` int(11) DEFAULT NULL,
      `complement` varchar(100) DEFAULT NULL,
      `longitude` varchar(5) DEFAULT NULL,
      `latitude` varchar(5) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `id_country_to_address_country_id` (`id_country`),
      KEY `id_state_to_address_state_id` (`id_state`),
      KEY `id_city_to_address_city_id` (`id_city`),
      KEY `ua_id_country_to_ac_country_id` (`id_country`),
      CONSTRAINT `ua_id_city_to_acy_id` FOREIGN KEY (`id_city`) REFERENCES `address_city` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `ua_id_country_to_ac_id` FOREIGN KEY (`id_country`) REFERENCES `address_country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `ua_id_state_to_as_id` FOREIGN KEY (`id_state`) REFERENCES `address_state` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `address_country` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(2) NOT NULL,
  `country` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hiode_UNIQUE` (`code`),
  UNIQUE KEY `country_UNIQUE` (`country`)
) ENGINE=InnoDB AUTO_INCREMENT=248 DEFAULT CHARSET=utf8;

CREATE TABLE `address_state` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `state` varchar(70) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `state_UNIQUE` (`state`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

CREATE TABLE `address_city` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(70) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `city_UNIQUE` (`city`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;