jeudi 24 septembre 2015

Designing you SQLite DB in anticipation of your future code?

Here is my situation: A senior member/boss in a company using my app will be able to assign "tasks" to his/her employees. A "task" may contain many "sub-tasks". Right now my only requirement is to make a "task" and a "sub_task" table in my database like this:

CREATE TABLE `tasks` (
`id`    INTEGER NOT NULL,
`title` TEXT NOT NULL,
`description`   TEXT NOT NULL,
PRIMARY KEY(id)
)

and sub_tasks:

CREATE TABLE `sub_tasks` (
`id`    INTEGER NOT NULL,
`title` TEXT NOT NULL,
`description`   TEXT NOT NULL,
`tasks_id`  INTEGER NOT NULL,
PRIMARY KEY(id)
FOREIGN KEY(tasks_id) REFERENCES tasks(id)
)

As you can see I use tasks_id in sub_tasks table as a foreign key referenced to id in tasks table. Right now it serves my requirement well. But in future when I will create an Employee class, the situation will be like this: A Senior member will/can assign many tasks to a single employee and every task may/may not contain many sub_tasks. How can you design you database in anticipation of this requirement so that it will not break down my code?

Aucun commentaire:

Enregistrer un commentaire