SQLiteManager: Defining relationship tables

Defining relationships between tables

If you need to create relationship tables (in case that you need to map two tables together for example) there are also systems in place for that!

One-to-many (1-n) relations

Let's take our "User" class from the previous example: A user might create posts, and we want to link these posts back to the user. For this there is another attribute available: [SqliteForeignKey(string childTableName)]. The variable "childTableName" refers to the name our relationship table will have in our database. You can either set this attribute only on the parent class (in our case "User", since it holds a list of children) or for two-way linking you can also set the attribute on the children class, as shown in this example.

Lets define our "Post" class and extend our "User" class!

[SqliteTable]
class User : DatabaseEntry<User>
{
	[SqliteUnique]
	public string Guid {get; set; }
	
	public string Name { get; set; }
	
	public DateTime Birthday { get; set; }
	
	[SqliteNotNull]
	public string Password { get; set; }
	
	[SqliteDefaultValue(true)]
	public bool DarkMode {get; set; }
	
	[SqliteIgnore]
	public Image ProfilePicture {get; set; }
	
	[SqliteForeignKey("mapping_user_posts")] // Tell SQLiteManager that our relationship table will be called "mapping_user_posts"
	public List<Post> Posts {get; set; } // Contains all posts made by this user
	
	public User(TableDefinition<User> table): base(table) { } // Default constructor; required by DatabaseEntry<T>
	
	// Constructors, methods, etc...
}

[SqliteTable]
class Post : DatabaseEntry<Post>
{
	[SqliteNotNull]
	public string Title {get; set; }
	
	[SqliteNotNull
	public string Content { get; set; }
	
	[SqliteNotNull]
	public DateTime CreationDate { get; set; }
	
	[SqliteForeignKey("mapping_user_posts")] // Tell SQLiteManager that our relationship table will be called "mapping_user_posts"
	public User Author { get; set; ] // Contains the parent for this post
	
	public User(TableDefinition<User> table): base(table) { } // Default constructor; required by DatabaseEntry<T>
	
	// Constructors, methods, etc...
}

And with this everytime a user creates a post and we save our user object to the database, SQLiteManager will automatically save their posts to the database and also update our relationship table!

One-to-one (1-1) relations

Once again we will use our "User" class from before as an example: Let's say we want to allow users to configure more than just toggling dark mode on or off. Of course we can just add more and more properties to our user which reflect our settings, but this can get confusing over time. A better approach would be to create a separate class to store user settings in:

[SqliteTable]
class UserSettings : DatabaseEntry<UserSettings>
{
        [SqLiteDefaultValue()]
        public bool DarkMode { get; set; }

        public bool ShowOffline { get; set; }

        public string Location {get; set; }

        public UserSettings(TableDefinition<UserSettings> table) : base(table)
        {

        }
	
	// Constructors, methods, etc...
}

Now that we have our class for storing user settings, let's adapt our "User" class:

[SqliteTable]
class User : DatabaseEntry<User>
{
        // ... existing code
	
        [SqliteForeignKey()] // Tell SQLiteManager to treat this object as a 1-1 relation to the table storing "UserSettings" objects
	public UserSettings UserSettings {get; set; }
}

Many-to-many (n-m) relations

Let's say we want to give our users the possibility to join one or more communities. In this case a user might belong to one or more communities, while a community may have one or multiple users in it. For this puprose the [SqliteForeignKey] attribute has yet another constructor available. Let's adapt our User class and create a new class called "Community":

[SqliteTable]
class Community : DatabaseEntry<Community>
{
        [SqliteNotNull()]
        public string Name { get; set; }

        [SqliteForeignKey("mapping_users_communities", true)] // By adding "true" we tell SqliteManager to treat this relation as a n-m relation
        public List<User> Users { get; set; } = new List<User> Users;

        public Community(TableDefinition<Community > table) : base(table)
        {

        }
	
	// Constructors, methods, etc...
}

[SqliteTable]
class User : DatabaseEntry<User>
{
        // ... existing code

        [SqliteForeignKey("mapping_users_communities", true)] // By adding "true" we tell SqliteManager to treat this relation as a n-m relation
        public List<Community> Communities { get; set; } = new List<Community>();
}

Side note: You can also define n-m relations inside the same class. This is useful if for example we want to allow users to add friends. In this case we proceed like before, and define a list in our "User" class:

[SqliteTable]
class User : DatabaseEntry<User>
{
        // ... existing code

        [SqliteForeignKey("mapping_users_friends", true)] // By adding "true" we tell SqliteManager to treat this relation as a n-m relation
        public List<User> Friends { get; set; } = new List<User>();
}

When saving or loading a user from the database, SQLiteManager will automatically save and load the state of our "UserSettings" object. Inside the table for our user there will be a new column for storing the ID of the "UserSettings" object, which acts as the foreign key.

---

Next up: Selecting data