Database Design – Code First

3 minutesCreating a database is a very complex task which requires us to think about the structure of project tables and relations between them and to design them optimally. Entity Framework offers three approaches:  
  • CodeFirst;
  • Database First;
  • Model First.
  I have chosen the CodeFirst approach. This approach, in my opinion, is optimal in our project, because:  
  • I don’t like any kind of designers and defining mapping in EDMX;
  • Full control over the code (no autogenerated code which is hard to modify);
  • General expectation is that you do not bother with DB. DB is just a storage with no logic;
  • Database version control. We may migrate between database versions (This makes it very easy for us to go back to the selected point and restore an earlier database version.) This approach is very flexible and we do not waste time restoring a database from a copy or sql file (only structure database if we want a default value in database we must use OnModelCreating method in class ApplicationDbContext ).
We must remember to never change database manually; only use command migration after a change in the poco classes or in the ApplicationDbContext class. I used a Microsoft.AspNetCore.Identity.EntityFrameworkCore.IdentityUser class to authorize users. This solution will allow me to receive access functionality to applications without creating my own database structure as well as a controller and views in the TooExe.Owl.Mvc. project A good practice is to create your own database in a separate schema. We obtain this by using the attribute:
[Table("KnownWords", Schema = "Owl")]
public class KnownWord
{
   [Key]
    public int Id { get; set; }

    public int IdTranslation { get; set; }
    public int IdOwlUser  { get; set; }
    [ForeignKey("IdOwlUser")]
    public virtual OwlUser OwlUser { get; set; }
    [ForeignKey("IdTranslation")]
    public virtual Translation Translation { get; set; }
 }
After creating poco class in ApplicationDbContext class we write properties:
public DbSet<Article>Articles { get; set; }
public DbSet<ArticleDetail> ArticleDetails { get; set; }
public DbSet<EnglishWord> EnglishWords { get; set; }
public DbSet<KnownWord> KnownWords { get; set; }
public DbSet<PlayList> PlayLists { get; set; }
public DbSet<PlayListDetail> PlayListDetails { get; set; }
public DbSet<PolishWord>; PolishWords { get; set; }
public DbSet<Translation> Translations { get; set; }
public DbSet<OwlUser> OwlUser { get; set; }
After the compilation, we go to nuget console and we choose the assumptive TooExe.Owl.Mvc project (we set the same assumptive project in Solution explorer) and we write Add-Migration NazwaAktualnegoPunktuBazy (for example, DodaniePolaAdres). When it is added we update the database using Update-Database. Database Diagram: Diagram ASP .NET Identity: With CodeFirst, we will be able to quickly modify the database based on future functionality. Of course, if the system was in production and its performance was a priority, then I would use the Database First approach, and it would not be a relational database. One would consider using a noSQL database, but that is not in our project, which is a proof of technology project.