Designing an SQL Table and getting it right the first time

Question

I currently working on an issue tracker for my company to help them keep track of problems that arise with the network. I am using C# and SQL.

Each issue has about twenty things we need to keep track of(status, work loss, who created it, who's working on it, etc). I need to attach a list of teams affected by the issue to each entry in my main issue table. The list of teams affected ideally contains some sort of link to a unique table instance, just for that issue, that shows the list of teams affected and what percentage of each teams labs are affected.

So my question is what is the best way to impliment this "link" between an entry into the issue table and a unique table for that issue? Or am I thinking about this problem wrong.

Answer 1

If I understand the question correctly I would create....

• ISSUE table containing the 20 so so items
• TEAM table containing a list of teams.
• TEAM_ISSUES table containing the link beteen the two

The TEAM_ISSUES table needs to contain a foriegn key to the ISSUE and TEAM tables (ie it should contain an ISSUE_ID and a TEAM_ID... it therefore acts as an intersection between the two "master" tables. It sounds like this is also the place to put the percentage.

Does that make sense?

Answer 2

We can see those entities in your domain:

1. The "Issue"
2. "Teams" affected by that issue, in a certain percentage

So, having identified those two items, you can represent that with two tables, and the relationship between them is another table, that could track the percentage impact too.

Hope this helps.

Answer 3

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

1. One for issues, with one record (row) per each individual unique issue created...

2. One for the teams, with one record for each team in your company...

3. And one table called say, "IssueTeams" or "TeamIssueAssociations" `or "IssueAffectedTeams" to hold the association between the two...

   This last table will have one record (row) for each team an issue affects... This table will have a 2-column composite primary key, on the columns IssueId, AND TeamId... Every row will have to have a unique combination of these two values... Each of which is individually a Foreign Key (FK) to the Issue table, and the Team Table, respectively.

For each team, there may be zero to many records in this table, for each issue the team is affected by,

and for each Issue, there may be zero to many records each of which represents a team the issue affects.

Answer 4

I wouldn't create a unique table for each issue. 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

Answer 5

Unless I'm understanding wrong what you're trying to do, you should not have a unique table for each instance of an issue.

Your database should have three tables: an Issues table, a Teams table, and an IssueTeams joining table. The IssueTeams table would include foreign keys (i.e. TeamID and IssueID) that reference the respective team in Teams and issue in Issues. So Issue Teams might have records like (Issue1, Team1), (Issue1, Team3). You could keep the affected percentage of each teams' labs in the joining table.

Answer 6

What you are describing is called a "many-to-many" relationship. A team can be affected by many issues, and likewise an issue can affect many teams.

In SQL database design, this sort of relationship requires a third table, one that contains a reference 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)
);

Answer 7

Well, just to be all modern and agile-y, 'getting it right the first time' is less trendy than 'refactorable.' But to work through your model:

You have Issues (heh heh). You have Teams.

An Issue affects many Teams. A Team is affected by many Issues. So just for the basic problem, you seem to have a classic Many:Many relationship. A join table containing two columns, one to Issue PK and one to Team PK takes care of that.

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

Answer 8

If I understand you correctly, you want to create a new table of teams affected for each issue. Creating tables as part of normal operations rings my relational database design alarm bell. Don't do it!

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

Answer 9

There are so many good free open source issue trackers available that you should have pretty good reasons for implementing your own. You could use your time much better in customizing an existing tracker.

We are using Bugtracker.NET in the team I work for. It's been customized quite a bit, but there was no point in developing a system from the beginning. The reason we chose that product was that it runs on .NET and works great with SQL Server, but there are many other alternatives.

Answer 10

I am a newbie. It was a great article to get me started. Spoke about things I hadn't anticipated and this type of technical stuffs would create interests to learn more and perform more in this field of technology