Barbara Stafford wrote:
> Hi, I am creating my first little application using php and mysql. I am working on the best way
> to structure my tables in the database.
I would use the following schema:
USE test;
DROP TABLE IF EXISTS companies;
CREATE TABLE companies (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL
);
INSERT INTO companies (name) VALUES ('Company 1');
DROP TABLE IF EXISTS main_tasks;
CREATE TABLE main_tasks (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL
);
INSERT INTO main_tasks (name) VALUES
('Administration'), ('Preparations'), ('Improvements'), ('Final');
DROP TABLE IF EXISTS sub_tasks;
CREATE TABLE sub_tasks (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
task_id INT NOT NULL,
name VARCHAR(40) NOT NULL
);
INSERT INTO sub_tasks (task_id, name) VALUES
(1, 'company survey'), (1, 'impact report'), (1, 'technical report'), (1, 'biological report'),
(2, 'stack review'), (2, 'submit review'), (2, 'comment review'), (2, 'plan a'), (2, 'plan b'),
(3, 'design plan for a'), (3, 'design plan for b'),
(4, 'review final'), (4, 'submit final'), (5, 'approve final');
DROP TABLE IF EXISTS persons;
CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10) NOT NULL
);
INSERT INTO persons (name) VALUES
('Barbara');
DROP TABLE IF EXISTS worklog;
CREATE TABLE worklog (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
company_id INT NOT NULL,
subtask_id INT NOT NULL,
start_date DATE NOT NULL,
start_resp_id INT NOT NULL,
finish_date DATE NOT NULL,
finish_resp_id INT NOT NULL,
notes TEXT
);
# To record info about Task 'Administration', Subtask 'Company survey' for 'Company 1', you do
INSERT INTO worklog
(company_id, subtask_id, start_date, start_resp_id, finish_date, finish_resp_id, notes) VALUES
(1, 1, '2005-08-08', 1, '2005-08-15', 1, 'A note');
--
felix
Please use
BBCode to format your messages in this forum.