How to store a list in SQL Server database

Question

I am pretty new with databases, I've been working on a school windows forms .NET project in C# and I came to a problem.

I have a table in a SQL Server database with patients (animals) and I need to add a list (unknown length) of vaccines dosages that each patient has gotten. Each animal will have different length and values in its list of vaccines.

Can someone please tell me how can I store the list in the database column?

Answer 1

So you have a table of Animals and a table of Vaccines

class Animal
{
    public int Id {get; set;}
    public string Name {get; set;}

    ... // other properties, like BirthDate
}

class Vaccine
{
    public int Id {get; set;}
    public string Name {get; set;}

    ... // other properties
}

You give an Animal a dosage of a Vaccine. To remember which Animal got which dosage, you need a table VacineDosages. In this table, you can see which Animal got a Dosage of which Vaccine. You probably want to know on which Date the Animal got the dosage, and maybe the amount of the dosage.

There is a one-to-many relation between Animals and VaccineDosages: Every Animal got zero or more VaccineDosages, every VaccineDosage has been given to exactly one Animal

Similarly there is also a one-to-many relation between Vaccines and VaccineDosages: Every Vaccine has been used zero or more times in a VaccineDosage, every VaccineDosage is a Dosage of exactly one Vaccine.

When using a relational database, one-to-many relations are implemented using a foreign key. The item on the "many" side gets a foreign key to the item on the "one" side that it belongs to.

Every VaccineDosage has been used to vaccinate exactly one Animal; the dosage "belongs" to this one Animal, hence it gets a foreign key to this Animal. Similarly, a VaccineDosage belongs to a Vaccine, and thus has a foreign key

class VaccineDosage
{
    public int Id {get; set;}

    // foreign keys
    public int AnimalId {get; set;}
    public int VaccineId {get; set;}

    ... // other properties, like Vaccination Date and Amount
}

Now to add VaccineDosages, you need to know to Which Animal the VaccineDosage was given, and with Which Vaccine. Well, you don't need to know the Animal and Vaccine, only their Ids will do.

So Suppose you have a sequence of VaccineDosages that are not added to the database yet, so they don't have an Id yet.

Id  AnimalId VaccineId  Amount     Date
 0     10       23        10    2021-05-10
 0     10       24         5    2021-05-10
 0     12       23        10    2021-05-09
 0     13       23        10    2021-05-10
 etc.

Or, in C# classes:

IEnumerable<VaccineDosage> vaccineDosages = new []
{
    new VaccineDosage
    {
         AnimalId = 10,
         VaccineId = 23,
         Amount = 10,
         Date = new DateTime(2021,05,10),
    },
    new VaccineDosage
    {
         AnimalId = 10,
         VaccineId = 24,
         Amount = 5,
         Date = new DateTime(2021, 05, 10),
    },
    ... etc
}

Now how to add this list to the database? The method depends on what method you use to communicate with the database:

• LOW level: using SQL, and add the values parameters one by one
• Intermediate level: using nuget package DAPPER: you only need to supply the SQL and the object
• High level: using entity Framework.

Of course, as a good programmer you want to hide where and how you save your data: it can be in a database, but maybe you want to do it in an XML file for unit tests, of CSV, Json?

All that users want to know: I want to put items in an object, and later, even after a restart of the computer, I can fetch the items again from the same object.

This "Warehouse" thing, is quite often called a repository:

class Repository
{
    public int AddAnimal(Animal animal} {...}
    public int AddVaccine(Vaccine vaccine {...}
    public int AddDosage(VaccineDosage dosage) {...}

These methods return the newly created Id of the added item, so later you can fetch them by Id:

    public Animal FetchAnimal(int animalId) {...}

You add methods to the repository as needed:

    public IEnumerable<Animal> FetchAllAnimals() {...}
    public IEnumerable<Animal> FetchAnimalsOfOwner(int ownerId) {...}
    public IEnumerable<Animal> FetchUnvaccinateAnimals(int vaccineId) {...}

etc.

In your case, you also want a method to add a sequence of VaccineDosages.

public void AddDosages(IEnumerable<VaccineDosage> dosages) {...}

Maybe all VaccineDosages are inserted in the same Animal, or on the same date, if that is the case, consider to create overloads:

public void AddDosages(int animalId, IEnumerable<Vaccine> vaccins, ...)

To make life easy for you, these overloads can call your original version. You probably won't be adding 1000 vaccins per seconds.

The nice thing of the repository is that you hide how the data is saved. For a small project and unit tests you can save it as a CSV file, or XML. Later you can safely change it to save in a database using SQL, You can change the type of database, or the method used to communicate with the DBMS. Users of your repository don't have to change, and thus don't need to be tested again.

LOW Level: SQL with parameters

If you are still learning databases, it is a good idea to start low level, so you get the feeling which code is done "under the hood" when you use higher level methods

When communicating with the database at low level, you create a Database Connection. You ask the connection to create a command, and you fill the command with the SQL text and the parameters. Finally you execute the command.

Usually you don't want to have an open database connection for a longer period of time. You make one procedure that opens and closes the database connection for one action: adding one VaccineDosage, or a fairly small sequence of VaccinedDosages.

If you need to Add a million VaccineDosages to the database in one call, you are talking about bulk usages. This might require a different approach. This is out of scope of your question.

A database connection has a connection string. It depends on which database management system (DBMS) you use, whether you need to provide this connection string or not. If you don't provide it, the application uses the connection string that is provided in file app.config.

To hide which actual DBMS you use, consider to create a factory method that creates the proper database connection for you:

public DbConnection CreateDatabaseConnection()
{
    // for example: use SQLight as database:
    string dbConnectionString = this.CreateDbConnectionString();
    return new System.Data.SQLite.SQLiteConnection(dbConnectionString);
}

If later you decide to use a different DBMS, you'll only have to change this procedure.

Hide the names of the tables, so you can change them later:

private const string tableNameAnimals = "Animals";
private const string tableNameVaccines = "Vaccines";
private const string tableNameVaccineDosages = "VaccineDosages";

public void Add(VaccineDosage dosage)
{
    const string sqlText = @"Insert into table " + tableNameVaccineDosages
        + " (AnimalId, VaccineId, Amount, Date)"
        + " Values(@AnimalId, @VaccineId, @Amount, @Date);"

    using (var dbConnection = this.CreateDatabaseConnection())
    {
        dbConnection.Open();
        using (var dbCommand = dbConnection.CreateDbCommand())
        {
            dbCommand.CommandText = sqlText;

            // add all parameters:
            dbCommand.Parameters.AddWithValue("@AnimalId, dosage.AnimalId);
            dbCommand.Parameters.AddWithValue("@VaccineId, dosage.VaccineId);
            ... // add the other parameters

            // Execute the command:
            dbCommand.ExecuteNonQuery();
        }
    }
}

The using statements assert that all items are closed and thrown away correctly, as soon as not used anymore

If you want to add a bunch of VaccineDosages, you can call this method several times. Or a slight optimization, reuse the dbCommand

public void Add(IEnumerable<VaccineDosage> dosages)
{
    const string sqlText = @"Insert into table " + tableNameVaccineDosages
        + " (AnimalId, VaccineId, Amount, Date)"
        + " Values(@AnimalId, @VaccineId, @Amount, @Date);"

    using (var dbConnection = this.CreateDatabaseConnection())
    {
        dbConnection.Open();         
        foreach (var dosage in dosages)
        {
            using (var dbCommand = dbConnection.CreateDbCommand())
            {
                 ... etc.
            }
        }
    }
}

Of course you could omit using parameters and create one sql text that contains the complete sql command, but that might be dangerous: people could destroy your database. See SQL Injection attack

Always try to use a constant sql string as text. Don't edit the sql string, to add values for parameters, always use Parameters.AddWithValue

Answer 2

A core principle of relational database design (database normalization process) is a column should contain atomic data.

Rather than storing multiple values (vaccine dosages) in a column, store those as rows in a separate related table (animal foreign key). That will inherently provide a list of varying and unlimited size.

If you have different types of vaccines, you should probably have additional tables as well.

Answer 3

You don't want to store multiple values in a column. That is not the SQLish way to do things.

Instead, you want multiple tables. Possibly something like this:

create table animals (
    animal_id int identity(1, 1) primary key,
    . . .    -- other information about the animal
);

create table vaccinations v (
    vaccination_id int identity(1, 1) primary key,
    animal_id int not null references animals(animal_id),
    vaccination_name varchar(255),
    dosage varchar(255),
    given_at datetime,
    . . .   -- perhaps other information
);

Note that the list of vaccinations might be stored in a separate table, perhaps with a separate row for each dosage. Your question doesn't have enough information to determine if that is the case.

Also note that there are multiple items of information for a given vaccination, such as the date/time given, who gave the vaccination, and so on.

Answer 4

Animal table

this table will store data about animal

ID	animal_Name
1	Simba
2	mando

vaccines table

this table will store data about vaccines

ID	Vaccine_Name
1	vaccine1
2	vaccine2
3	vaccine3

Animal-vaccin table

This Relation table will store All vaccines have given to each Animal

animal_ID	vaccine_ID	given_At	dosage
1	1	15/5/2012	5-Mg
1	2	5/9/2020	9-Mg
2	1	6/1/2021	20-Mg
1	1	6/4/2021	6-Mg

from this table you can see that Simba has taken vaccine1 twice and also take vacine2 only one time while mando take only vaccine1 one time

SQL

create table animals (
    ID int identity(1, 1) primary key,
    animal_Name varchar(200) not null
);

create table vaccines (
    ID int identity(1, 1) primary key,
    Vaccine_Name varchar(200)
);

create table Animal-vaccin(
   animal_ID int not null references animals(ID),
   vaccine_ID int not null references vaccines(ID),
   dosage varchar(200),
   given_At datetime

);