How to Store Multiple Values in Single Field in SQL Database

How to store multiple values in single field in SQL database?

You could use a second table to store the numbers, and link back with a Foreign Key:

PersonTable: PersonId, Name, etc..

The second table will hold the numbers...

NumbersTable: NumberId, PersonId(fk), Number

You could then get the numbers like this...

SELECT p.Name, n.Number from PersonTable p Left Join NumbersTable n
on p.PersonId = n.PersonId

This is a simple example. I have used a LEFT JOIN here in case a person doesn't supply their number. Also, this is just pseudo code, so don't use Table in the name.

Storing multiple values for a single field in a database

You're supposed to create an ADDRESS table that has a foreign key linking it to a PERSON record, i.e. PERSON_ID. This is the "relational" component of a relational database, and it's why it's more flexible than a flat file (which is like a single table).

How to store multiple values in one column SQL

You can store multiple values in a column for one row in many ways; but there may be a better solution.

For example, if you made the column able to hold a large string, you could store data as CSV, tab-separated, XML, JSON, etc. However, by storing the data like that, it will be very difficult to examine that data when writing SQL queries. Luckily, the thing which makes databases so powerful is how they can relate data together.

In your case, you want to store multiple weights against one combination of lot and part number. This is called a one-to-many relationship. You would have a table for the lot and part number, where each row has a unique identifier (this would be the primary key of this table).

Another table would store the weights, with one row for each weight for each row in the other table. Each row in the second table references a specific row in the first table by having a column in the second table containing the value in the unique identifier column from the first table (this is called a foreign key).

Once you have done this, your data is now normalised. You can now use joins to combine the data together. And use aggregates to find things like the SUM, MIN, MAX, etc.

How can I store multiple values in SQL?

Use a third table borrowed books which would link books and users

How can i store multiple values on a single mysql column?

This is a very simplistic view but gives an idea of a typical structure for this.

users

id	name
1	User 1
2	User 2

How to store multiple values in a single column in a database?

You're not breaking down the idea of a branch and rooms quite correctly. Based on your objects, you're indicating that multiple branches could have the same address since you store the branch names as a collection, but only one address. This means several branches would also have the same room information, which is also inaccurate. You also don't just want to store a collection of room numbers because then you have to do more work to get the details about that room.

Here's a better breakdown:

public class Branch
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public List<Room> Rooms { get; set; }
}

public class Room
{
    public int Id { get; set; }
    public int BranchId { get; set; }
    public int Number { get; set; }
    public int Capacity { get; set; }
    public bool Available { get; set; }
}

You have one object for the Branch, simply called Branch, which has all the details specific TO that branch. The same applies to the Room details. Inside the branch object you have a collection of Room objects to store the details about all the rooms. By adding a BranchId value to the room object you indicate that room and its details only apply to the branch at which it's actually located, and now Room can be expanded to include information that branch and anyone looking to use it would find relevant. Also makes it easy to quickly obtain all the related rooms when you get any given branch's details.

storing multiple values in column in mysql

How about create a table name projects and employer_projects, when you need all the projects that user has joined, just query from table projects JOIN with table employer_projects. Table employer_projects will store the connection between employees and projects;

employees <--> employer_projects <--> projects

Example:

Query all project of an employer_id with employer_id

SELECT * FROM projects p JOIN employer_projects ep ON p.id = ep.project_id WHERE ep.employer_id = ?

That could be easier for your to search & query in the future!

Here is some sample sql to insert new record
(ID is auto increment by DB right?)

Add new employees

INSERT INTO `employees`(`field_1`, `field_2`, `field_3`, ...) VALUES ('value 1', 'value 2', 'value 3', ...);

Add new project

INSERT INTO `projects` (`name`) VALUES ('test');

Add new employer_projects connection

INSERT INTO `employer_projects` (`employer_id`, `project_id`) VALUES ('1', '2');

Are you using PhpMyAdmin? All of the sample sql will available in tab SQL

One last thing. If the value and field data type in DB is int, you dont need an quote ' around value like '1', just 1 is okay.

How to Store Multiple Values in Single Field in SQL Database