database design question - adding 'other' field

Posted on 2005-05-06
Last Modified: 2010-03-19
This is a generic database design question. But I didn't see a topic where I could post this. Since I am using SQL Server, I thought I would post it here.
I am designing a 'help desk' site and I have a master table called 'Requests'. I have another table called 'FieldsToInclude' which contain the following fields:

FieldsToIncludeId (PK)
E-mail Address
Business Address

I have a form that will list these fields as checkboxes along with a textbox next to the 'other' checkbox so that the user can specify what 'other' field they want included.
I am having a hard time creating a design for this scenario. I have created another table called FieldstoIncludeLookup which has the following fields:

RequestId (FK to the master table's PK)
FieldsToIncludeId (FK to FieldsToInclude.FieldsToIncludeId)

However, I can't think of a way to include the text contained in the 'other' textbox if somebody selects the 'other' checkbox.

Please let me know if I should post this question elsewhere.
Question by:IUAATech
    LVL 68

    Accepted Solution

    I suggest perhaps something like this, although anything from just a quick look naturally needs further review/study before implementation.  :

    Table: IncludeColumns

    --unique id for this specific include column name
    Id  {PK; INT, IDENTITY(1,1))}
    -- include column name
    Name  {VARCHAR(whatever)}
        --values: "FullName"|"E-mail Address"|"Business Address"|"<user-entered text>"|...
    --type of data to be put in the included column
    Type  {VARCHAR(whatever)}
        --for example: "VARCHAR(30)", "INT", "DATETIME", etc.
    -- who can "see"/is eligible to add this "other" column.  Allows a "private" column to be added if desired
    SeenBy  {TINYINT}  
        --default: 0 = anyone can add this column (like FullName, E-mail Address, etc.)

    Table: RequestIncludeColumns

    RequestId {INT} (FK to the Requests.Id)
    IncludeId {INT} (FK to IncludeColumns.Id)
    --I think this columns might be useful later for upgrading/downgrading/changing tables
    Status {SMALLINT}  --Active/Inactive
    DateChanged  {DATETIME}

    [Personally, I think you should stay away from the term "fields" when dealing with a relational db: "field" and "column" have different connotations and, indeed, meanings.]

    Author Comment

    thanks for the suggestion. However, I don't see an answer on how to deal with the textbox that is next to the 'other' checkbox as I described in my original posting.
    LVL 68

    Expert Comment


    My intent was that the "IncludeColumns" table is intended to hold all included/added columns.  So anytime a new column name is placed in "Other", a new row with that name should be inserted into IncludeColumns.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now