I’m trying to develop a small app that basically helps track jobs and status of paperwork for these jobs once they are complete.
It’s basically 4 tables:
-
Job
-
Store
-
Crew (person who completes the job)
-
Status
The way it should be structured is:
-
One crew can have multiple jobs, but one job cannot have multiple crews (one to many / HAS_MANY relation).
-
Multiple jobs can be for one store, but one job cannot be for multiple stores (one to many / HAS_MANY relation).
-
One job can have one current status and vice versa (one to one / HAS_ONE relation).
The following is what I designed, but my constraints are wrong so it’s incorrect:
CREATE TABLE job (
work_order INT(6) NOT NULL PRIMARY KEY,
svc_date DATETIME,
resvc_date DATETIME,
level VARCHAR(3),
description TEXT,
crew_code VARCHAR(6) NOT NULL,
store_id INT(11),
INDEX(crew_code),
INDEX(store_id)
) ENGINE = InnoDB CHARSET=latin1;
CREATE TABLE status (
status VARCHAR(64) NOT NULL,
act_comment TEXT,
clr_comment TEXT,
work_order INT(6) NOT NULL,
CONSTRAINT FOREIGN KEY(work_order) REFERENCES job(work_order)
)ENGINE = InnoDB CHARSET=latin1;
CREATE TABLE crew (
crew_code VARCHAR(6) NOT NULL PRIMARY KEY,
phone VARCHAR(14),
email VARCHAR(64),
INDEX (crew_code),
CONSTRAINT FOREIGN KEY(crew_code) REFERENCES job(crew_code)
) ENGINE = InnoDB CHARSET=latin1;
CREATE TABLE store (
id INT(11) PRIMARY KEY AUTO_INCREMENT,
account VARCHAR(3) NOT NULL,
secondary_code VARCHAR(64),
number VARCHAR(11),
address VARCHAR(64),
address2 VARCHAR(64),
city VARCHAR(64),
state VARCHAR(2),
zip VARCHAR(11),
phone VARCHAR(14),
INDEX (id),
CONSTRAINT FOREIGN KEY(id) REFERENCES job(store_id)
) ENGINE = InnoDB CHARSET=latin1;
How do I modify this SQL to create such relations and set proper constraints in SQL? Further more, the following is what I get for Job model when I use Gii to generate it:
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'crew' => array(self::HAS_ONE, 'Crew', 'crew_code'),
'statuses' => array(self::HAS_MANY, 'Status', 'work_order'),
'store' => array(self::HAS_ONE, 'Store', 'id'),
);
}
and this is what Store model looks like:
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'id0' => array(self::BELONGS_TO, 'Job', 'id'),
);
}
Both of these are wrong.
Can someone please explain my mistakes?
Thank you.
P.S. To mods: I realize that this is mixture of Yii and MySql, so if in your opinion this question belong in another forum, please feel free to move it.