2

I've got the privilege to create a small in-house application for managing TODO-lists, although I am not really a developer (I know some PHP and SQL, and got a chance to prove myself).

As my experience is not the best, I ask for some opinions/suggestions on how to improve an issue with "imaginary" and real users.

Imagine you have company sections with people working on projects, sometimes they move between sections or quit the company. If a person moves or quits, their data should not disappear or be moved from the project they worked on.

I solved this by having a jointable(?) between Users and Sections. If a user moves or quits, I can just change the relation to Users. I can also "create" users who'll never login, but are used for tracking purpose.

Everything was fine until I started adding Projects and TODOs. All users should not have access to all projects and some should be admins for a project. To solve that I added yet another "jointable" (Profile).

That solution seems to work but feels clumsy, and I am curious to know if there is a better solution to model it.

I supply my current model to give a clearer picture (still working on it, like assigning users to TODO).

-- this table is readonly and updated using "triggers" from another database
CREATE TABLE "users" (
 "id" integer NOT NULL PRIMARY KEY,
 "email" varchar(32) NOT NULL,
 "firstname" varchar(64) NOT NULL,
 "lastname" varchar(64) NOT NULL
);
-- section within a company
CREATE TABLE "section" (
 "id" integer NOT NULL PRIMARY KEY,
 "name" varchar(64) NOT NULL
);
-- section have members, which can only belong to single section
CREATE TABLE "member" (
 "id" integer NOT NULL PRIMARY KEY,
 "section_id" integer NOT NULL REFERENCES "section" ("id"),
 -- NULL is permitted, because the member may not have an account
 "user_id" integer REFERENCES "users" ("id"),
 -- "email" is used as identifier if there is not account
 "email" varchar(254) NOT NULL
);
-- each section have project, but not shared between sections
CREATE TABLE "project" (
 "id" integer NOT NULL PRIMARY KEY,
 "section_id" integer NOT NULL REFERENCES "section" ("id"),
 "name" varchar(128) NOT NULL,
);
-- to connect a member to a project, they have a profile
CREATE TABLE "profile" (
 "id" integer NOT NULL PRIMARY KEY,
 "project_id" integer NOT NULL REFERENCES "project" ("id"),
 "member_id" integer NOT NULL REFERENCES "member" ("id"),
 "isadmin" bool NOT NULL
);
-- todo's within a project. actually would like to "assign"
-- people to it, but havent figured out best method yet
CREATE TABLE "todo" (
 "id" integer NOT NULL PRIMARY KEY,
 "project_id" integer NOT NULL REFERENCES "project" ("id"),
 "complete" bool NOT NULL,
 "title" varchar(128) NOT NULL,
 "datewhendone" datetime
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.