My Table structure for user and his adress detail is as follows
CREATE TABLE tbl_users (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
loginname varchar(128) NOT NULL,
enabled enum("True","False"),
approved enum("True","False"),
password varchar(128) NOT NULL,
email varchar(128) NOT NULL,
role_id int(20) NOT NULL DEFAULT '2',
name varchar(70) NOT NULL,
co_type enum("S/O","D/O","W/O") DEFAULT "S/O",
co_name varchar(70),
gender enum("MALE","FEMALE","OTHER") DEFAULT "MALE",
dob date DEFAULT NULL,
maritalstatus enum("SINGLE","MARRIED","DIVORCED","WIDOWER") DEFAULT "MARRIED",
occupation varchar(100) DEFAULT NULL,
occupationtype_id int(20) DEFAULT NULL,
occupationindustry_id int(20) DEFAULT NULL,
contact_id bigint(20) unsigned DEFAULT NULL,
signupreason varchar(500),
PRIMARY KEY (id),
UNIQUE KEY loginname (loginname),
UNIQUE KEY email (email),
FOREIGN KEY (role_id) REFERENCES tbl_roles (id),
FOREIGN KEY (occupationtype_id) REFERENCES tbl_occupationtypes (id),
FOREIGN KEY (occupationindustry_id) REFERENCES tbl_occupationindustries (id),
FOREIGN KEY (contact_id) REFERENCES tbl_contacts (id)
) ENGINE=InnoDB;
CREATE TABLE tbl_contacts (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
contact_type enum("cres","pres","coff"),
address varchar(300) DEFAULT NULL,
landmark varchar(100) DEFAULT NULL,
district_id int(11) DEFAULT NULL,
city_id int(20) DEFAULT NULL,
state_id int(20) DEFAULT NULL,
pin_id bigint(20) unsigned DEFAULT NULL,
area_id bigint(20) unsigned DEFAULT NULL,
po_id bigint(20) unsigned DEFAULT NULL,
phone1 varchar(20) DEFAULT NULL,
phone2 varchar(20) DEFAULT NULL,
mobile1 varchar(20) DEFAULT NULL,
mobile2 varchar(20) DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (district_id) REFERENCES tbl_districts (id),
FOREIGN KEY (city_id) REFERENCES tbl_cities (id),
FOREIGN KEY (state_id) REFERENCES tbl_states (id)
) ENGINE=InnoDB;
CREATE TABLE tbl_states (
id int(20) NOT NULL AUTO_INCREMENT,
name varchar(70) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE tbl_districts (
id int(20) NOT NULL AUTO_INCREMENT,
name varchar(70) DEFAULT NULL,
state_id int(20) DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (state_id) REFERENCES tbl_states (id)
) ENGINE=InnoDB;
CREATE TABLE tbl_cities (
id int(20) NOT NULL AUTO_INCREMENT,
name varchar(70) DEFAULT NULL,
district_id int(20) DEFAULT NULL,
state_id int(20) DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (district_id) REFERENCES tbl_districts (id),
FOREIGN KEY (state_id) REFERENCES tbl_states (id)
) ENGINE=InnoDB;
The relationship is as follows
User has multiple contacts i.e Permanent Address, Current Address, Office Address.
Each Contact has state and City.
User-Contact-state like this
How to save models of this structure in one go.
Please provide a reply ASAP
