What are the best db development methods when I want to save a value that is either selected from a drop-down list or entered by the user?

Question

I am trying to find a better way to create a database in order to resolve the following scenario:

• The user is presented with a drop-down list of universities (for example)
• The user selects his university from the list, if it exists
• If the university does not exist, it must enter its university in the text box (for example, another: [___________])

how can I create a database to handle this situation, given that I can sort, for example, using the university identifier (perhaps only for built-in universities, and not for those that were entered by users).

thanks!

I just want to make it look like Facebook is dealing with this situation. If the user selects his education (actually by typing a combo box that is not my problem) and choosing one of the return values, what would Facebook do?

In my guess, it will insert the UserID and EducationID into the many-to-many table. Now, what if the user types in is not in the database? He is still stored in his profile, but where? typing "St" ... offering Stanford http://i43.tinypic.com/hufztt.jpg

entering a non-existent university http://i41.tinypic.com/14j56s8.jpg

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 Unis on the system and 0 for user-entered unis.

Answer 2

You can fool: if you don’t care about the referential integrity of this field (that is, it simply appears in the user profile and is not required for strictly enforced business rules), save it as a simple VARCHAR column.

For a drop-down list, use a query, for example:

  SELECT DISTINCT (University) FROM Profiles

If you want to filter out typos or disposable ones, 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 to store trade descriptions of contracting companies; since this is only informational (there is a separate "Category" field to enforce business rules), this is an acceptable solution.

Answer 3

Keep the flag for lines entered through user input in the same table where you have other data points. Then you can sort the checkbox.

Answer 4

One way to solve this problem in the previous company I worked on:

Create two columns in the table: 1) the null identifier of the row provided by the system (stored in a separate table) 2) the row provided by the user

Only one of them is full. A constraint can provide this (and, in addition, if at least one of these columns is populated, if necessary).

It should be noted that the problem that we solved with this was the real situation of the Other. This is a textual description of an item with some predefined defaults. Your situation sounds like a real entity that is not on the list, st more than one user may want to enter the same university.

Answer 5

This is not a database design issue. This is a user interface problem.

The universities drop-down list is based on the rows in the table. This table should have a new row inserted when the user enters a new university in the text box.

If you want to separate the list that you have provided from those added by users, you can have a column in the university table indicating the origin (or origin) of the data.

Answer 6

I am not sure the question here is very clear.

I did this quite a few times at work and just select either a drop-down list of a text field. If the data is entered in a text field, I first insert it into the database and then use IDENTITY to get a unique identifier for this inserted row for subsequent queries.

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

This contradicts MS SQL 2008, but I'm not sure if global @@ SCOPE_IDENTITY () exists in other versions of SQL, but I'm sure there are equivalents.