/*
created		3/14/2013
modified		5/4/2013
project		
model		
company		
author		
version		
database		mysql 4.0 
*/


create table account (
	email varchar(100) not null,
	fullname varchar(200) not null,
	password varchar(35) not null,
	gender bit not null,
	dayofbirth varchar(30) not null,
	country varchar(30),
	avatar varchar(50) not null,
	coverphoto varchar(50) not null,
	isblock bit not null default false,
	orthername varchar(100),
	slogan text,
	aboutus text,
 primary key (email)) ;

create table professtion (
	idpro varchar(10) not null,
	email varchar(100) not null,
	companyname varchar(200) not null,
	city varchar(50),
	designation varchar(30) not null,
	startyear varchar(4) not null,
	endyear varchar(4),
	isworkinghere bit not null default false,
 primary key (idpro)) ;

create table relationshiptype (
	idreltype varchar(10) not null,
	relationtype varchar(50) not null,
 primary key (idreltype)) ;

create table relationship (
	idreltype varchar(10) not null,
	emailfrom varchar(100) not null,
	emailto varchar(100) not null,
	requestdate varchar(30) not null default getdate(),
	responsedate varchar(30),
	isaccept bit not null default false,
	isviewed bit not null default false,
	idshare varchar(5) not null,
 primary key (emailfrom,emailto)) ;

create table anotheraccount (
	idotheracc varchar(10) not null,
	idacctype varchar(10) not null,
	email varchar(100) not null,
 primary key (idotheracc)) ;

create table accounttype (
	idacctype varchar(10) not null,
	type varchar(100) not null,
 primary key (idacctype)) ;

create table phone (
	idphone varchar(10) not null,
	email varchar(100) not null,
	number varchar(20) not null,
 primary key (idphone)) ;

create table place (
	idplace varchar(10) not null,
	email varchar(100) not null,
	address varchar(200) not null,
	city varchar(50),
	startyear varchar(4) not null,
	endyear varchar(4),
	livinghere bit not null default false,
 primary key (idplace)) ;

create table liking (
	email varchar(100) not null,
	hobbies varchar(200),
	musics varchar(200),
	movies varchar(200),
	books varchar(200),
 primary key (email)) ;

create table album (
	idalbum varchar(10) not null,
	idshare varchar(5) not null,
	email varchar(100) not null,
	name varchar(100) not null,
	datecreate varchar(30) not null default getdate(),
	description varchar(200),
 primary key (idalbum)) ;

create table linkphotos (
	idphoto varchar(10) not null,
	idalbum varchar(10) not null,
	link char(20),
	discription varchar(200),
	datecreate varchar(30) not null,
 primary key (idphoto)) ;

create table sharetype (
	idshare varchar(5) not null,
	discription varchar(20) not null,
 primary key (idshare)) ;

create table status (
	idstatus varchar(20) not null,
	email varchar(100) not null,
	idshare varchar(5) not null,
	content text not null,
	datecreate varchar(30) not null default getdate(),
 primary key (idstatus)) ;

create table commentstatus (
	idcomstatus varchar(20) not null,
	idstatus varchar(20) not null,
	email varchar(100) not null,
	content text not null,
	commentdate varchar(30) not null default getdate(),
 primary key (idcomstatus)) ;

create table messagesend (
	idmsgsend varchar(20) not null,
	idmsg varchar(20) not null,
	emailsend varchar(100) not null,
	emailto varchar(100) not null,
	datesend varchar(30) not null default getdate(),
	content varchar(200) not null,
	isviewed bit not null default false,
 primary key (idmsgsend,emailsend,emailto)) ;

create table messagetype (
	idmsg varchar(20) not null,
	contentmsg varchar(250) not null,
 primary key (idmsg)) ;

create table statuslike (
	idstatus varchar(20) not null,
	emaillikestatus varchar(100) not null,
	datelikestatus bit not null default false) ;

create table commentphoto (
	idcomphoto varchar(20) not null,
	idphoto varchar(10) not null,
	email varchar(100) not null,
	content text,
	commentdate varchar(30) not null default getdate(),
 primary key (idcomphoto)) ;

create table groups (
	idgroup varchar(20) not null,
	groupname varchar(250) not null,
	icongroup varchar(250) not null,
	description text,
	datecreate varchar(30) not null,
 primary key (idgroup)) ;

create table membergroup (
	email varchar(100) not null,
	idgroup varchar(20) not null,
 primary key (email,idgroup)) ;

create table filesgroup (
	idfile varchar(20) not null,
	namefile varchar(250) not null,
	linkfile varchar(250) not null,
	dateupload varchar(30) not null,
	email varchar(100) not null,
	idgroup varchar(20) not null,
 primary key (idfile)) ;

create table groupstatus (
	idgstatus varchar(20) not null,
	content text not null,
	datecreate varchar(30) not null default getdate(),
	email varchar(100) not null,
	idgroup varchar(20) not null,
 primary key (idgstatus)) ;

create table groupstatuslike (
	datelikestatus bit not null default false,
	idgstatus varchar(20) not null,
	email varchar(100) not null,
	idgroup varchar(20) not null,
 primary key (idgstatus,email,idgroup)) ;

create table groupcommentstatus (
	idgcomstatus varchar(20) not null,
	content text not null,
	commentdate varchar(30) not null default getdate(),
	idgstatus varchar(20) not null,
	email varchar(100) not null,
	idgroup varchar(20) not null,
 primary key (idgcomstatus)) ;

create table newinfo (
	id char(10) not null,
	idtable char(10) not null,
	nametable char(50) not null,
	datecreate char(15) not null,
 primary key (id)) ;

create table admin (
	adminname char(30) not null,
	adminpass char(35) not null,
 primary key (adminname)) ;


alter table professtion add foreign key (email) references account (email) on delete  restrict on update  restrict;

alter table relationship add foreign key (emailfrom) references account (email) on delete  restrict on update  restrict;

alter table relationship add foreign key (emailto) references account (email) on delete  restrict on update  restrict;

alter table anotheraccount add foreign key (email) references account (email) on delete  restrict on update  restrict;

alter table phone add foreign key (email) references account (email) on delete  restrict on update  restrict;

alter table place add foreign key (email) references account (email) on delete  restrict on update  restrict;

alter table liking add foreign key (email) references account (email) on delete  restrict on update  restrict;

alter table album add foreign key (email) references account (email) on delete  restrict on update  restrict;

alter table status add foreign key (email) references account (email) on delete  restrict on update  restrict;

alter table commentstatus add foreign key (email) references account (email) on delete  restrict on update  restrict;

alter table messagesend add foreign key (emailsend) references account (email) on delete  restrict on update  restrict;

alter table messagesend add foreign key (emailto) references account (email) on delete  restrict on update  restrict;

alter table statuslike add foreign key (emaillikestatus) references account (email) on delete  restrict on update  restrict;

alter table commentphoto add foreign key (email) references account (email) on delete  restrict on update  restrict;

alter table membergroup add foreign key (email) references account (email) on delete  restrict on update  restrict;

alter table relationship add foreign key (idreltype) references relationshiptype (idreltype) on delete  restrict on update  restrict;

alter table anotheraccount add foreign key (idacctype) references accounttype (idacctype) on delete  restrict on update  restrict;

alter table linkphotos add foreign key (idalbum) references album (idalbum) on delete  restrict on update  restrict;

alter table commentphoto add foreign key (idphoto) references linkphotos (idphoto) on delete  restrict on update  restrict;

alter table status add foreign key (idshare) references sharetype (idshare) on delete  restrict on update  restrict;

alter table album add foreign key (idshare) references sharetype (idshare) on delete  restrict on update  restrict;

alter table relationship add foreign key (idshare) references sharetype (idshare) on delete  restrict on update  restrict;

alter table commentstatus add foreign key (idstatus) references status (idstatus) on delete  restrict on update  restrict;

alter table statuslike add foreign key (idstatus) references status (idstatus) on delete  restrict on update  restrict;

alter table messagesend add foreign key (idmsg) references messagetype (idmsg) on delete  restrict on update  restrict;

alter table membergroup add foreign key (idgroup) references groups (idgroup) on delete  restrict on update  restrict;

alter table groupstatus add foreign key (email,idgroup) references membergroup (email,idgroup) on delete  restrict on update  restrict;

alter table filesgroup add foreign key (email,idgroup) references membergroup (email,idgroup) on delete  restrict on update  restrict;

alter table groupcommentstatus add foreign key (email,idgroup) references membergroup (email,idgroup) on delete  restrict on update  restrict;

alter table groupstatuslike add foreign key (email,idgroup) references membergroup (email,idgroup) on delete  restrict on update  restrict;

alter table groupstatuslike add foreign key (idgstatus) references groupstatus (idgstatus) on delete  restrict on update  restrict;

alter table groupcommentstatus add foreign key (idgstatus) references groupstatus (idgstatus) on delete  restrict on update  restrict;


/* users permissions */


