What is the best practices in database design when I want to store a value that is either selected from a dropdown list or entered by the user if not found in the dropdown list?

Answer 1

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.

Answer 2

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 3

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.

Answer 4

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 5

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.

Answer 6

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.