Xamarin SQLite - ManyToMany relationship

Question

I have 4 classes that i deal with in my Xamarin.Forms project: Users, User_Profiles, Medicines and Medicne_Incident.

Business rules:

• Each User has 1 User_Profile (User profile is basically a detailed description of that user, User_Profile inherits from User)
• There is a ManyToMany relation between Users and the medicines, and so a bridge table is created: Medicine_Incident
• A user can have multiple Medicine_Incidents, but that incident is unique to the user.

I have no idea how to connect the dots (i.e., to have a stable relationship diagram for them).

Below are the class definitions for each of them:

User:

public class User
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
    public bool ProfileComplete { get; set; }

    public User()
    {
        ProfileComplete = false;
        Username = "";
        Email = "";
        Password = "";
    }

    public User(string email, string Password)
    {
        this.Email = email;
        this.Password = Password;
    }
}

User_Profiles:

public class User_Profiles : User
{
    public string Gender { get; set; }
    public string Country { get; set; }
    public string Address { get; set; }
    public string Province { get; set; }
    public string Postal { get; set; }

    public User_Profiles()
    {
        //Medicine = new List<Medicine>();
    }

    //?Do we need this?
    public User_Profiles(string email, string Password)
    {
        //Medicine = new List<Medicine>();
        this.Username = "";
        //this.Dob = DateTime.Today;//Probably wrong to do
        this.Gender = "";
        this.Country = "";
        this.Address = "";
        this.Province = "";
        this.Postal = "";

        //Password/Email for User
        this.Email = email;
        this.Password = Password;
    }
}

Medicine:

public class Medicine //: User_Profiles
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string Medicine_Name { get; set; }
    public string Medicine_Desc { get; set; }

    public Medicine()
    {
        Medicine_Name = "";
        Medicine_Desc = "";
        //Medicine_Incident = new List<Medicine_Incident>();
    }
}

Medicine_Incident:

public class Medicine_Incident : Medicine
{
    public virtual Medicine Medicine { get; set; }
    public virtual User_Profiles User_Profiles { get; set; }
    public DateTime Time { get; set; }
    public int Dosage { get; set; }

    public Medicine_Incident()
    {
        // (1/1/0001 12:00:00 AM)
        Dosage = 0;
    }
}

I do my database querying in a file called "UserDatabase.cs":

public class UserDatabase
{
    readonly SQLiteAsyncConnection database;

    public UserDatabase(string dbPath)
    {
        database = new SQLiteAsyncConnection(dbPath);
        database.CreateTableAsync<User_Profiles>().Wait();
        database.CreateTableAsync<User>().Wait();
        database.CreateTableAsync<Medicine>().Wait();
        database.CreateTableAsync<Medicine_Incident>().Wait();
    }

    public void SaveProfileAsync(User_Profiles user)
    {
        database.InsertAsync(user);
    }

    public Task<User_Profiles> GetProfileAsync(string email, string pass)
    {
        return database.Table<User_Profiles>().Where(i => (i.Email.Equals(email)) && (i.Password.Equals(pass))).FirstOrDefaultAsync();
    }

    public void SaveMedicineAsync(Medicine med)
    {
        database.InsertAsync(med);
    }

    public void SaveMedicineIncidentAsync(Medicine_Incident med_inc)
    {
        database.InsertAsync(med_inc);
    }

    public Task<Medicine_Incident> GetMedicineIncidentAsync(string email, string medName, DateTime time)
    {
        return database.Table<Medicine_Incident>().Where(i => (i.User_Profiles.Email.Equals(email)) && (i.Medicine.Medicine_Name.Equals(medName)) && (i.Time.Equals(time))).FirstOrDefaultAsync();
    }
}

Now the main thing is, i know i am doing it wrong, but how can i rectify it? Trust me when i say i spent time solving this and finding solution to this online.

NOTE:
- I am using SQLite.
- I have the SQLite extension in the project.

Answer 1

Sorry for the poor english

There is a lot of problems here. I guess you should read this and this article to help you learn how to model class/database in your code, but here is some tips:

 

• Inheritance is used to extend a class (for example: Human inherits from mammal that inherits from animal that inherits from living being)

• Your User_Profile class can have a public List Users (that must be ignored by your SQLite database) and a Id [PrimaryKey]

• Your User class, the IdUser_Profile (this will define which profile each user have). It can be reffered to your UserProfile class...

I hope that it helps you.

Edit

You're right. This will not help you. I let it pass some details about your scope. Here is a more usefull information:

The only problem I see is the Medicine_Incident class. I would do so:

public class Medicine_Incident
{
    [SQLite.Net.Attributes.PrimaryKey, SQLite.Net.Attributes.AutoIncrement]
    public int Id { get; set; }
    public int IdMedicine { get; set; }
    [SQLite.Net.Attributes.Ignore]
    public Medicine Medicine { get; set; }
    public int IdUser_Profiles { get; set; }
    [SQLite.Net.Attributes.Ignore]
    public User_Profiles User_Profiles { get; set; }
    public DateTime Time { get; set; }
    public int Dosage { get; set; }

    public Medicine_Incident()
    {
        // (1/1/0001 12:00:00 AM)
        Dosage = 0;
    }
}

And in your database query:

public class UserDatabase
{
    readonly SQLiteAsyncConnection database;

    public UserDatabase(string dbPath)
    {
        database = new SQLiteAsyncConnection(dbPath);
        database.CreateTableAsync<User_Profiles>().Wait();
        // Don't do that (Your User's attribute will be persisted within your User_Profiles table): database.CreateTableAsync<User>().Wait();
        database.CreateTableAsync<Medicine>().Wait();
        database.CreateTableAsync<Medicine_Incident>().Wait();
    }

    public void SaveProfileAsync(User_Profiles user)
    {
        database.InsertAsync(user);
    }

    public Task<User_Profiles> GetProfileAsync(string email, string pass)
    {
        return database.Table<User_Profiles>().Where(i => (i.Email.Equals(email)) && (i.Password.Equals(pass))).FirstOrDefaultAsync();
    }

    public void SaveMedicineAsync(Medicine med)
    {
        database.InsertAsync(med);
    }

    public void SaveMedicineIncidentAsync(Medicine_Incident med_inc)
    {
        database.InsertAsync(med_inc);
    }

    public Task<Medicine_Incident> GetMedicineIncidentAsync(string email, string medName, DateTime time)
    {
        var user = database.Table<User_Profiles>().Where(u => u.Email == email).FirstOrDefault();
        var medicine = database.Table<Medicine>().Where(m => m.Medicine_Name == medName).FirstOrDefault();
        var medInc = database.Table<Medicine_Incident>().Where(mi => mi.IdUser_Profiles == user.Id && mi.IdMedicine == medicine.Id).FirstOrDefault();

        medInc.User = user;
        medInc.Medicine = medicine;

        return medInc;
    }
}

SQLite doesn't handle relationships between classes when querying. To do this, use the SQLite Net Extensions.

I hope it helps you.