Clever Geek Handbook

Designing a SQL table and getting it right the first time

I am currently working on Issue Tracking for my company to help them track issues that occur on the network. I am using C # and SQL.

Each issue has about twenty things that we need to track (status, job loss, who created it, who is working on it, etc.). I need to attach a list of the commands affected by the issue to each entry in my main issue table. The list of affected groups ideally contains some link to a unique instance of the table, just for this problem, which shows a list of affected groups and what percentage of each lab lab is affected.

So my question is the best way to inject this "link" between the issue table entry and the unique table for this issue? Or I am thinking about this problem in the wrong way.

+1


source to share


9 replies


What you are describing is called a many-to-many relationship. A group can be affected by many issues, and a problem can affect many teams.

In SQL database design, this kind of relationship requires a third table that contains a link to each of the other two tables. For example:



CREATE TABLE teams (
  team_id INTEGER PRIMARY KEY
  -- other attributes
);

CREATE TABLE issues (
  issue_id INTEGER PRIMARY KEY
  -- other attributes
);

CREATE TABLE team_issue (
  issue_id INTEGER NOT NULL,
  team_id INTEGER NOT NULL,
  FOREIGN KEY (issue_id) REFERENCES issues(issue_id),
  FOREIGN KEY (team_id) REFERENCES teams(team_id),
  PRIMARY KEY (issue_id, team_id)
);
+14


source


This sounds like a classic many-to-many relationship ... You probably need three tables,

  • One for problems with one entry (line) for each separate unique problem created ...

  • One for teams, with one entry for each team in your company ...

  • And one table is called "IssueTeams" or "TeamIssueAssociations" or "IssueAffectedTeams" to keep the relationship between the two ...

    This last table will have one record (row) for each team affected by the problem ... This table will have a 2 column composite primary key, on the IssueId, AND TeamId columns ... Each row will need to have a unique combination of these two values ​​... Each represents an individual foreign key (FK) in the problem table and command table, respectively.



For each command, this table can have from zero to many entries, for each issue the command affects,

and there can be zero to many entries for each problem, each one representing a command that is affected by the problem.

+3


source


If I understood the question correctly, I would create ....

  • ISSUE table containing 20 such elements
  • TEAM table containing the list of commands.
  • TEAM_ISSUES table containing a link between the two

The TEAM_ISSUES table must contain the foriegn key for the ISSUE and TEAM tables (that is, it must contain the ISSUE_ID and TEAM_ID ... so it acts as the intersection between the two "leading" tables. Also a place to put the percentage.

It makes sense?

+2


source


There are so many good free open source trackers out there that you should have pretty good reason to implement your own. You could use your time much better in customizing your existing tracker.

We use Bugtracker.NET on the team I work for. It was configured quite a bit, but there was no point in designing the system from the beginning. The reason we chose this product was because it runs on .NET and works great with SQL Server, but there are many other alternatives.

+2


source


We can see these objects in your domain:

  • " Problem "
  • " Teams " affected by this issue, in a specific percentage

So, having identified these two elements, you can imagine it with two tables, and the relationship between them is another table that can also track the percentage impact.

Hope it helps.

+1


source


I would not create a unique table for every problem. I would do something like this

Table: Issue
IssueId primary key
status
workLoss
createdby
etc

Table: Team
TeamID primary key
TeamName
etc

Table: IssueTeam
IssueID (foreign key to issue table)
TeamID (foreign key to team table)
PercentLabsAffected
+1


source


If I don't understand what you are trying to do, you shouldn't have a unique table for each instance of the problem.

Your database should have three tables: the Issues table, the Teams table, and the IssueTeams connection table. The IssueTeams table will include foreign keys (that is, TeamID and IssueID) that refer to the corresponding team in Teams and Issues in Issues. Therefore, problem groups may have entries such as (Issue1, Team1) (Issue1, Team3). You can save the affected lab percentage of each group in a connection table.

+1


source


Well, just to be all modern and nimble, "get it right the first time" is less fashionable than "refactorable. But to work through your model:

You have problems (hehe). You have commands.

The problem affects many Teams. Many problems were raised by the group. So for the main problem, you seem to have the classic Many: Many relationship. A junction table containing two columns, one for the PC issuance and one for the Team PK will take care of this.

Then you have a question about what% commands. There's a dynamic aspect, of course, so in order to do it right you need to specify a trigger. But the obvious place for this is in the column in Issue ("Affected_Team_Percentage").

+1


source


If you understand correctly, you want to create a new table of commands affected for each issue. Creating tables as part of normal operations triggers the wake-up call with the relational database. Do not do this!

Instead, use the affected_teams table with a foreign key to the issues table and a foreign key to the teams table. This will do the trick.

0


source






More articles:


All Articles