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?

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.


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.