Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 my 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.

All 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
);

Many thanks for any advice you can give.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.