CODEWITHSUNDEEP
databasedatabase-designdrop-down-menu
sabbour
sabbour

Reputation: 5009

What is the best practices in db design when I want to store a value that is either selected from a dropdown list or user-entered?

I am trying to find the best way to design the database in order to allow the following scenario:

  1. The user is presented with a dropdown list of Universities (for example)
  2. The user selects his/her university from the list if it exists
  3. If the university does not exist, he should enter his own university in a text box (sort of like Other: [___________])

how should I design the database to handle such situation given that I might want to sort using the university ID for example (probably only for the built in universities and not the ones entered by users)

thanks!

I just want to make it similar to how Facebook handles this situation. If the user selects his Education (by actually typing in the combobox which is not my concern) and choosing one of the returned values, what would Facebook do?

In my guess, it would insert the UserID and the EducationID in a many-to-many table. Now what if the user is entering is not in the database at all? It is still stored in his profile, but where? typing "St"...suggesting Stanford

typing non-existing university

Upvotes: 8

Views: 2355

Answers (6)

Keith Williams
Keith Williams

Reputation: 2357

You could cheat: if you're not worried about the referential integrity of this field (i.e. it's just there to show up in a user's profile and isn't required for strictly enforced business rules), store it as a simple VARCHAR column.

For your dropdown, use a query like:

SELECT DISTINCT(University) FROM Profiles

If you want to filter out typos or one-offs, try:

SELECT University FROM PROFILES
GROUP BY University
HAVING COUNT(University) > 10  -- where 10 is an arbitrary threshold you can tweak

We use this code in one of our databases for storing the trade descriptions of contractor companies; since this is informational only (there's a separate "Category" field for enforcing business rules) it's an acceptable solution.

Upvotes: 2

Giraffe
Giraffe

Reputation: 2013

One way this was solved in a previous company I worked at:

Create two columns in your table: 1) a nullable id of the system-supplied string (stored in a separate table) 2) the user supplied string

Only one of these is populated. A constraint can enforce this (and additionally that at least one of these columns is populated if appropriate).

It should be noted that the problem we were solving with this was a true "Other:" situation. It was a textual description of an item with some preset defaults. Your situation sounds like an actual entity that isn't in the list, s.t. more than one user might want to input the same university.

Upvotes: 1

Svante Svenson
Svante Svenson

Reputation: 12478

CREATE TABLE university
(
  id smallint NOT NULL,
  name text,
  public smallint,
  CONSTRAINT university_pk PRIMARY KEY (id)
);

CREATE TABLE person
(
  id smallint NOT NULL,
  university smallint,
  -- more columns here...
  CONSTRAINT person_pk PRIMARY KEY (id),
  CONSTRAINT person_university_fk FOREIGN KEY (university)
      REFERENCES university (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

public is set to 1 for the Unis in the system, and 0 for user-entered-unis.

Upvotes: 6

Kieran Senior
Kieran Senior

Reputation: 18220

I'm not sure if the question is very clear here.

I've done this quite a few times at work and just select between either the drop down list of a text box. If the data is entered in the text box then I first insert into the database and then use IDENTITY to get the unique identifier of that inserted row for further queries.

INSERT INTO MyTable Name VALUES ('myval'); SELECT @@SCOPE_IDENTITY()

This is against MS SQL 2008 though, I'm not sure if the @@SCOPE_IDENTITY() global exists in other versions of SQL, but I'm sure there's equivalents.

Upvotes: -2

Learning
Learning

Reputation: 8185

Keep a flag for the rows entered through user input in the same table as you have your other data points. Then you can sort using the flag.

Upvotes: 1

S.Lott
S.Lott

Reputation: 391852

This isn't a database design issue. It's a UI issue.

The Drop down list of universities is based on rows in a table. That table must have a new row inserted when the user types in a new University to the text box.

If you want to separate the list you provided from the ones added by users, you can have a column in the University table with origin (or provenance) of the data.

Upvotes: -1

Related Questions