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?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.