I am currently designing an issue database for my company. The back-end is a MySQL server 4.0. The main table "Issues" consists of the fields "IssueID" (varchar 8), "IssueDescription" (varchar 120), "IssueEnteredBy" (varchar 20), "IssueRequestedBy" (varchar 20), "IssueResponsibility" (varchar 20).
Now, I would like to add a field for a detailed description ("IssueDetails") with a length of 8192 varchars.
Should I add a seperate table just for the details, or should I include it into the "Issues" table? I am wondering because of performance reasons. This database will grow very large after a while.
Do you think that Issue Details will be entered for each and every issue (not necessary 8000 + characters)? If yes, then it would make sense in keeping it in the same table (saves you from having to do an outer join). If issue details is not mandatory and only entered for a very few issues, then it may make sense to keep it on a separate table along with Issue ID. Mind you, this will require you to do an outer join with the issues detail table in case you wanted to select all issues entered by a particular user (for ex).
Consider the scenario where similar issues will arise for each IssueID as the DB grows.
Do you want to categorize these issues in the future? It sounds as if the DB will become large, and associated with this size will be the re-occurrence of the same issues. In this case, you would have a separate Issues table.
Yes, definetely. Most probably, you only need the issues description in some report or when each issue is viewed one at a time. Hope you don't plan on making this issues description searchable (i.e. something like where issues_desc like '%problem with server%' etc.). I am not sure of My SQL Server, but Oracle doesn't allow that (you can write procedures to get around that, but that is besides the point). And keeping the Issues description on another table may also make for faster inserts/positioned updates/deletes on the Issues table.