Results 1 to 5 of 5

Thread: Opinion Needed

  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: Opinion Needed

    Hey everyone..

    I am creating a small little bug tracking application to use as an example.

    So I have an issues table:

    Code:
    [dbo].[Issues](
    	[Title] [varchar](255),
    	[Description] [varchar](max),
    	[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?

    Thanks!

    -Nick

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Give us examples of what a severity would be and a status?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I would go with the tables, like you modelled.

    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
    Wim

    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

  4. #4
    Join Date
    Nov 2009
    Posts
    2
    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.

    Thanks for your input.

    -Nick

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No, you are thinking correctly, but missing the bigger point

    That Value of doing this, is, as you mentioned, adding valid alues along the way, but the bigger point is to ensure Referential Integrity

    Just understand, you will not be able to remove any code values if they still exists in Issues...which is not really a big deal
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •