I am creating a small little bug tracking application to use as an example.
So I have an issues table:
[LastUpdated] [datetime] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[SeverityId] [int] NOT NULL,
[StatusId] [int] NOT NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Issues_1] PRIMARY KEY CLUSTERED
I would have both a 'Severity' and a 'Status' table. These tables will have only a few records each in them. The point was to make this application extensible meaning I could add more status and severity records as they are requested by the users.
My Question: How should I design these one to one relationships to my 'Issues' table? Would it be considered a 'best practice' to create a FK for both within 'Issues' as I have it now? Or should I just make these columns Varchar datatypes and call it good?
Probably people will want to see the progress of a certain Issue. With your current set up, you will loose your historic data: when was the issue first reported and by who, who assigned the severity and when, when was is marked as accepted for development, development started, ready for test, ...
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
Thank you for the reply. The 'Status' and 'Severity' tables will have nothing more than a 'Description' and a primary key. I just wanted to break them off into their own tables so if either needed an additional value I wasn't changing markup code every time (a dropdown list for example). My thoughts were that if these requirements changed (and you know they will) I could just add another row to the table(s) and see the corresponding form(s) propagate with these changes.
I only pose the question because, in code, this means a relationship is mapped between the 'Issues' entity and both 'Severity' and 'Status'. Seems almost overkill. At the same time, writing a varchar value to my Issues table for each of these is redundant.