CODEWITHSUNDEEP
database-designdatabase-schema
OneRandomCoder
OneRandomCoder

Reputation: 87

Database design; Dynamically creating tables

I need to create an application which tracks Projects. So I have a number of projects each of which will have attributes like ID, name, and owner. Each project will have many tasks under it. Each task will attributes like ID, Name, Completion Status, priority and owner.

Now when I design a DB for this application, two possible designs come to mind. I do not know which one is a better design. It would be great if you could let me know the better design among the ones below and please do not forget to mention WHY one scores over the other.

Here are the approaches.

Approach 1. Have 2 tables in the DB. One would be a project table, the other would be a task table. The task table will have a foreign key for Project ID, which links a particular task to the project it is part of. The basic differentiating factor with this approach with respect to the other below is that all task irrespective of which project it is part of will be stored in one and only one table whose length can keep growing indefinitely.

Approach 2. Have one Project table, and for each project, dynamically create a table that has tasks only for that project. Thus there will be as many task tables as there are rows in the project table. There could be an algorithm by which we arrive at the name of the dynamically created table. This logic can then be used to get the name of a particular table when we want to query it. The advantage that I see in this approach even though it looks cumbersome is that all tasks of a particular project will be only in one table whose length will not grow out of control(at least not as much as the above case). But the number of tables might keep growing indefinitely.

Upvotes: 0

Views: 2086

Answers (1)

Kermit
Kermit

Reputation: 34053

Approach 1 is better. You do not want to be creating a table for each project. This is anti-normalization. Your tables would look like:

(Pseudo-code)

CREATE TABLE project (
  projectId int NOT NULL PRIMARY KEY
  name varchar,
  owner varchar);

CREATE TABLE task (
  taskId int AUTO_INCREMENT PRIMARY KEY,
  projectId int NOT NULL,
  name varchar,
  completionStatus varchar,
  priority int,
  owner varchar,
  FOREIGN KEY projectId
    REFERENCES project.projectId)

Upvotes: 3

Related Questions