mysql - simple database design with foreign keys -

mysql - simple database design with foreign keys -

i'm trying design create app should maintain of track of completed jobs , status of paperwork these jobs. requires database (mysql) of next format:

5 tables:

job crew (person performs job) store (job performed @ location) status (is paperwork received?) comment (communication , notes)

the next how tables should relate:

one crew can have many jobs, 1 job cannot have many crews (one many) one store can have many jobs, 1 job cannot have many stores (one many) status belongs job (one one) comment belongs job (one one)

constraints:

job cannot exist without store, cannot exist without crew comments , status cannot exist without job

my sql:

use ppwk; create table store ( id int(11) auto_increment primary key, business relationship varchar(3) not null, secondary_account varchar(64), number varchar(11) not null, address varchar(64), address2 varchar(64), city varchar(64), state varchar(2), zip varchar(12), phone varchar(14) ); create table crew ( crew_code varchar(6) primary key not null, address varchar(64), address2 varchar(64), city varchar(64), state varchar(2), zip varchar(12), phone varchar(14), phone2 varchar(14), phone3 varchar(14), phone4 varchar(14), fax varchar(14), email varchar(64) ); create table job ( work_order int(6) primary key not null, svc_date date not null, resvc_date date, level varchar(2), description text, store_id int(11), crew_code varchar(6), foreign key (store_id) references store(id), foreign key (crew_code) references crew(crew_code) ); create table status ( work_order int(6) primary key, status varchar(64) not null, added_on datetime, closed_on datetime, foreign key (work_order) references job(work_order) on update cascade on delete cascade ); create table comment ( id int(11) primary key auto_increment, work_order int(6), act_comment text, clr_comment text, act_last_updated datetime, clr_last_updated datetime, index (work_order), foreign key(work_order) references job(work_order) on update cascade on delete cascade );

question 1: sql requirements are?

question 2: i'm confused foreign keys. if add together foreign key "job" points "store", turn "job" kid table belongs "store"? or other way around?

thank you.

as long store , crew exist, can create new job.

create table job ( idjob char(20) not null, idcrew char(20) not null, idstore char(20) not null, primary key (idjob,idcrew,idstore)) type = myisam row_format = default; create table crew ( idcrew char(20) not null, primary key (idcrew)) type = myisam row_format = default; create table store ( idstore char(20) not null, primary key (idstore)) type = myisam row_format = default; create table status ( idstatus char(20) not null, primary key (idstatus)) type = myisam row_format = default; create table comment ( idcomment char(20) not null, primary key (idcomment)) type = myisam row_format = default;

mysql sql database