more than one value for a column in database

Question

is it possible to store more than one value in database column. if yes which type i should use? and through my java code how can i insert the values.

for example i want to have a column "language" which can store values like java, c++, c# etc. for a single row.

EDIT : i want to have a table called student with all the information about a student with a column to store the names of languages he knows.

Answer 1

You should create a table "language" with all the different languages you want to use, and then use a foreign key to link to this table from a LanguageID column in your other table.

Edit: If you want more than 1 language for a given record then you will need to also create a linking table which links the record to a given LanguageID. Then you can put any number of different languages in for a given record by creating new records in the linking table.

Answer 2

Do you mean an enumeration, where only certain values can be used, or do you mean storing more than one value in a column for a given row?

Enumeration

-- Works in MySQL for sure
table whatever (
  language enum('JAVA','C++,'C#') NOT NULL DEFAULT 'JAVA'
};

Multiple Values

Do not do this, use a many-to-many relationship instead:

table whatever (
  id int
);
table language (
  id int,
  name varchar(256)
)
table whatever_language)(
  whatever_id int,
  language_id int
)
-- don't forget foreign key constraints

Answer 3

I think Zack answer is correct, but if you REALLY need to store multiple values in a database row, you can use the VARCHAR type and use a separator like ";" to separate the values, but you must be carefull when adding/editting these values to parse them correctly.

Or if you are using an Oracle Database, you can define the type of the column as an ARRAY, but this is a propietary solution which generates too much dependency, i don't recomend it.

Answer 4

You need to create a Language table as mentioned by Zack where each language is represented then have a table to link them to such as _-Langage (where _ is replaced with original table name)

This linking table will have an incremental index as its Primary key and will have a Foreign Key of the Language table and the Original table. Then there can be multiple rows in the linking table for each language needed in the original table

Answer 5

If you know about database design and want to get rid of this additional language table which is the usual "good" way to do it you can use for example a string.

In the string keep the ISO language code which is somethink like "en-en" (first country, second language variant") together with a separator token, obviously space might be a good one so that "en-en de-de " for english and german.

You can then use the "like" operator on this string column for complex multi language matching.

When you are a professional and know what you are doing you can call this an optimization. If your are a newbie then you obviously didn't know what you done and what the "relation" in relational database systems mean.

Answer 6

Storing several values in a single column is usually not a good idea, since it violates the principles of database normalization.

From your description it sounds like you have a many-to-many relationship. Implementing it by storing several values in a column would force you to work harder than necessary whenever you want to update the column, or query by a language.

It's technically possible to think of a workaround (as Telcontar suggested, for example), but I would recommend reading a bit about the different normal forms, and reconsidering the database design.

Answer 7

Use a join table (also called chain table) to define n-m relations.

User table (pseudo-SQL):

CREATE TABLE user {
    id INTEGER GENERATED PRIMARY KEY,
    name VARCHAR
}

Language table (pseudo-SQL):

CREATE TABLE language {
    id INTEGER GENERATED PRIMARY KEY,
    name VARCHAR
}

Join table for users and languages (pseudo-SQL):

CREATE TABLE user_language {
    user_id INTEGER FOREIGN KEY REFERENCES user(id),
    language_id INTEGER FOREIGN KEY REFERENCES language(id)
}

This way you can just get all languages by user ID (and all users which are tied to a specific language). Some RDBMS supports retrieval of those values as a SQL ARRAY type which you in turn can obtain by ResultSet#getArray() in a single query. In PostgreSQL for example you can do the following query:

SELECT u.id, u.name, ARRAY(
    SELECT l.name
    FROM language l
    JOIN user_language ul ON u.id = ul.user_id
    WHERE l.id = language_id) AS languages
FROM user u

which you can handle in JDBC as follows:

while (resultSet.next()) {
    Long id = resultSet.getLong("id");
    String name = resultSet.getString("name");
    Object[] languages = resultSet.getArray("languages").getArray();
    // Cast to String[] or convert to List<String> or so yourself.
}

Answer 8

What you're talking about is a many-to-many relationship between your table (you didn't give a name) and the languages.
Let me assume that your table is about people and that you want to track which people are proficient in which languages.
Any given person will have a proficiency in one (perhaps zero) or more languages. To model this, you need three tables: Person, Language and a third one that links the two, perhaps LanguageProficiency which has a reference to one person and one language. In this table, you can have multiple rows for the same person or the same language, but you should have only one entry per unique combination of person and language (i.e. these two columns form the primary key of this table).

The advantage of using a third table here (over some of the other suggestions such as concatenating them into a text field) is that it is easy to expand it to include additional attributes, such as how many years a person has used the language, or a rating for the level of mastery in a language, or some test score. These are things you would want to know for each combination of a person and language, so it belongs in the LanguageProficiency table.