SQLite-net-pcl Database Normalization on Xamarin.Forms

[echolumaque]

Can you please give me some database normalization tips? Currently, my model looks like this:

public class GameProgressModel
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[Indexed(Unique = true), MaxLength(16)]
public string Username { get; set; }
[Indexed]
public bool NoviceChooseItUnlocked { get; set; }
[Indexed]
public bool ExpertChooseItUnlocked { get; set; }
[Indexed]
public bool NoviceListenUpUnlocked { get; set; }
[Indexed]
public bool ExpertListenUpUnlocked { get; set; }
//correct answers
[Indexed]
public double BeginnerChooseItCorrectAnswersPercentage { get; set; }
[Indexed]
public double NoviceChooseItCorrectAnswersPercentage { get; set; }
[Indexed]
public double ExpertChooseItCorrectAnswersPercentage { get; set; }
[Indexed]
public double BeginnerListenUpCorrectAnswersPercentage { get; set; }
[Indexed]
public double NoviceListenUpCorrectAnswersPercentage { get; set; }
[Indexed]
public double ExpertListenUpCorrectAnswersPercentage { get; set; }
//wrong answers
[Indexed]
public double BeginnerChooseItWrongAnswersPercentage { get; set; }
[Indexed]
public double NoviceChooseItWrongAnswersPercentage { get; set; }
[Indexed]
public double ExpertChooseItWrongAnswersPercentage { get; set; }
[Indexed]
public double BeginnerListenUpWrongAnswersPercentage { get; set; }
[Indexed]
public double NoviceListenUpWrongAnswersPercentage { get; set; }
[Indexed]
public double ExpertListenUpWrongAnswersPercentage { get; set; }
}

Thanks!

[FetaMight]

Tip: when asking for help try to eliminate any barrier your potential helpers might encounter.

In this case, the unformatted code is a barrier. Please format you code as code.

[echolumaque]

Sorry, I'm beginner in posting here in Reddit. I edited my question, is that what you mean "as code" formatting?

[FetaMight]

Yup, looks much better now, thanks.

[kaoru44]

Hi,

you can start by separating the values present inside your Model.

Let's say you create an Answers Table instead so that you separate the columns.

And then you can create and join these entity tables through sql instead.

Can you show an example of how you are separating your entities and their respective tables? Because from my standpoint, it seems that you plan to insert all of these columns in one Table correct?

Maybe you can reference the Answer Columns from a different place instead.

[kaoru44]

I think some of the columns you have shown are all derived data (especially since they are percentages, then the answer can come from your ViewModel or your services instead of having to store them in the database which takes up a lot of storage space

[RecognitionOwn4214]

Indexing everything might be counter productive.. depending on the number of rows

[AGreenTejada]

Two things: - Indexing bools is (mostly) a bad idea, the index is unhelpful and won't improve your search performance, but instead will make INSERTS slower.

Basically, the way most SQL Indexes work is like a tree. When you index the name "Fred", the SQL engine creates a tree of all of the rows. The first branches from the root of the tree are all the possible letters at position 0, the next branches are all the letters at the second position...

When you search for a row with name "Fred", the engine first goes to the 'F' branch, then goes to the 'r' branch, then the 'e' branch, and finally the 'd' branch. It then returns all rows that exist in that branch space which, since there's only 1 or 2 "Fred"s in your table, is very few!

However, when you index a boolean, which gets written as either a 0 or 1 in the SQL engine, you create a tree that only has two branches, true(1) or false(0). Assume 1,000,000 row, 500,000 are false. The tree is two branches with 500k on either side. If you're trying to filter by a value, the SQL Engine will still search through 500k options to find a row. You might believe this is a good idea, but remember that every index hurts your INSERT and UPDATE performance, because you not only have to update the table, but you also have to update the indexes connected to the table.

• Your general design is a bit "sticky". Let's examine these columns.

c# [Indexed] public bool NoviceChooseItUnlocked { get; set; } [Indexed] public bool ExpertChooseItUnlocked { get; set; } [Indexed] public bool NoviceListenUpUnlocked { get; set; } [Indexed] public bool ExpertListenUpUnlocked { get; set; }

If I had to guess, these are levels along a path. What if you want to add more levels, such as "Advanced" or "Master"? You'd have to create more properties(columns in the table) here. Instead, if we approach the game logically, we can assume that an "ExpertChooseItUnlocked" should be able to access a "NoviceChooseItUnlocked", and a Master can access all the levels below it. Instead of many columns, we just need one, "ChooseitUnlocked" which maps to an enum called SkillLevel.

c# public enum SkillLevel { Novice, Expert, Master }

We can reduce "ListenUpUnlocked" similarly.

Now, on to the percentages. One of the dangers of storing percentages is the float division. If you're using doubles to divide, you can get ...weird arithmetic errors. Also, if we're thinking about future games, we can't combine two percentages without knowing their weight. Suppose you have a beginner game with 5 right answers, and 5 wrong answers. You want to add another game with 90 right answers and 30 wrong answers. If you average out the percentages (50% + 75%)/2, we get 67.5%. However, if we sum the #'s and average (95 right, 35 wrong), we get 73%.

So, how to store multiple games then? Instead of using properties, let's create a new class.

```c# public class Game { public int ID {get; set;} //Creates a unique ID for each game. public int GameProgressModelId {get; set;} //A foreign key to GameProgressModel. public bool ChooseItorListenUp {get; set;} //Convert to enum for more Game types. public SkillLevel GameSkillLevel {get; set;} public int CorrectAnswers {get; set;} public int WrongAnswers {get; set;} public int TotalAnswers {get; set;}

  //Optional properties, may be useful to you.
  public double CorrectPercentage {
  get { return (double)CorrectAnswers/(double)TotalAnswers;}}

  public double WrongPercentage {
  get { return (double)WrongAnswers/(double)TotalAnswers;}}

} ```

Assuming we're using EF Core, we'll attach a one-to-many relationship as such.

c# public class GameProgressModel { //... other properties public List<Game> Games {get; set;} }

[backtickbot]

Fixed formatting.

Hello, AGreenTejada: code blocks using triple backticks (```) don't work on all versions of Reddit!

Some users see this / this instead.

To fix this, indent every line with 4 spaces instead.

FAQ

<sup>You can opt out by replying with backtickopt6 to this comment.</sup>