아파트 거래 정보 조회 시스템 Database Table 설계

E-R Diagram 설계

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema goodhouse
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema goodhouse
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `goodhouse` DEFAULT CHARACTER SET utf8 ;
USE `goodhouse` ;

-- -----------------------------------------------------
-- Table `goodhouse`.`baseaddress`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `goodhouse`.`baseaddress` (
  `ADDR_CD` INT(11) NOT NULL AUTO_INCREMENT,
  `SIDO` VARCHAR(30) NOT NULL,
  `CODE` VARCHAR(30) NULL DEFAULT NULL,
  `DONG_CD` VARCHAR(30) NOT NULL,
  `GUGUN` VARCHAR(30) NOT NULL,
  `DONG_NM` VARCHAR(30) NOT NULL,
  `LAT` VARCHAR(20) NULL DEFAULT NULL,
  `LNG` VARCHAR(20) NULL DEFAULT NULL,
  PRIMARY KEY (`ADDR_CD`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `goodhouse`.`houseinfo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `goodhouse`.`houseinfo` (
  `INFO_CD` INT(11) NOT NULL AUTO_INCREMENT,
  `ADDR_CD` INT(11) NOT NULL,
  `DONG_NM` VARCHAR(30) NOT NULL,
  `APT_NM` VARCHAR(50) NOT NULL,
  `YEAR` VARCHAR(30) NULL DEFAULT NULL,
  `JIBUN` VARCHAR(30) NULL DEFAULT NULL,
  `LAT` VARCHAR(30) NULL DEFAULT NULL,
  `LNG` VARCHAR(30) NULL DEFAULT NULL,
  `IMG` VARCHAR(50) NULL DEFAULT NULL,
  PRIMARY KEY (`INFO_CD`),
  INDEX `ADDR_CD` (`ADDR_CD` ASC),
  CONSTRAINT `houseinfo_ibfk_1`
    FOREIGN KEY (`ADDR_CD`)
    REFERENCES `goodhouse`.`baseaddress` (`ADDR_CD`)
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `goodhouse`.`housedeal`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `goodhouse`.`housedeal` (
  `DEAL_NO` INT(11) NOT NULL AUTO_INCREMENT,
  `DEAL_AMT` VARCHAR(50) NOT NULL,
  `YEAR` VARCHAR(30) NOT NULL,
  `MONTH` VARCHAR(30) NOT NULL,
  `DAY` VARCHAR(30) NOT NULL,
  `AREA` VARCHAR(30) NOT NULL,
  `FLOOR` VARCHAR(30) NOT NULL,
  `TYPE` VARCHAR(30) NOT NULL,
  `INFO_NO` INT(11) NOT NULL,
  PRIMARY KEY (`DEAL_NO`),
  INDEX `INFO_NO` (`INFO_NO` ASC),
  CONSTRAINT `housedeal_ibfk_1`
    FOREIGN KEY (`INFO_NO`)
    REFERENCES `goodhouse`.`houseinfo` (`INFO_CD`)
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `goodhouse`.`member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `goodhouse`.`member` (
  `MEM_ID` VARCHAR(30) NOT NULL,
  `MEM_NM` VARCHAR(100) NOT NULL,
  `PWD` VARCHAR(100) NOT NULL,
  `EMAIL` VARCHAR(100) NOT NULL,
  `PHONE` VARCHAR(100) NOT NULL,
  `ADDR` VARCHAR(500) NOT NULL,
  PRIMARY KEY (`MEM_ID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `goodhouse`.`interest`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `goodhouse`.`interest` (
  `MEM_ID` VARCHAR(30) NOT NULL,
  `ADDR_CD` INT(11) NOT NULL,
  PRIMARY KEY (`MEM_ID`, `ADDR_CD`),
  INDEX `ADDR_CD` (`ADDR_CD` ASC),
  CONSTRAINT `interest_ibfk_1`
    FOREIGN KEY (`MEM_ID`)
    REFERENCES `goodhouse`.`member` (`MEM_ID`)
    ON UPDATE CASCADE,
  CONSTRAINT `interest_ibfk_2`
    FOREIGN KEY (`ADDR_CD`)
    REFERENCES `goodhouse`.`baseaddress` (`ADDR_CD`)
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `goodhouse`.`post`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `goodhouse`.`post` (
  `POST_NO` INT(11) NOT NULL AUTO_INCREMENT,
  `MEM_ID` VARCHAR(30) NOT NULL,
  `TITLE` VARCHAR(100) NOT NULL,
  `CONTENT` VARCHAR(2000) NOT NULL,
  `WRT_DT` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`POST_NO`),
  INDEX `MEM_ID` (`MEM_ID` ASC),
  CONSTRAINT `post_ibfk_1`
    FOREIGN KEY (`MEM_ID`)
    REFERENCES `goodhouse`.`member` (`MEM_ID`)
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

DDL File

EER-Diagram