[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

database design question - adding 'other' field

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.
  • 2
1 Solution
Scott PletcherSenior DBACommented:
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.]
IUAATechAuthor Commented:
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.
Scott PletcherSenior DBACommented:

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now