C
C#•4d ago
Keyvan

How far should i normalize my DB?

I am making a website which will help students with exam prep. Here we include past exams etc. For now we have user, exam, examsubmissions.
3 Replies
Keyvan
KeyvanOP•4d ago
Here are the schemas: 
namespace Server.Models
{
    public sealed class ExamAnswer
    {
        public Guid Id { get; set; } = 
Guid.NewGuid();       // UUID
        public string UserId { get; set; }      // Foreign key to user who this belongs to
        public DateTime CompletionDate { get; set; } = DateTime.UtcNow;
        public string Answer { get; set; }
        public Guid ExamId { get; set; }      // Foreign key to exam
    }
}
namespace Server.Models
{
    public sealed class ExamAnswer
    {
        public Guid Id { get; set; } = 
Guid.NewGuid();       // UUID
        public string UserId { get; set; }      // Foreign key to user who this belongs to
        public DateTime CompletionDate { get; set; } = DateTime.UtcNow;
        public string Answer { get; set; }
        public Guid ExamId { get; set; }      // Foreign key to exam
    }
}
using System.ComponentModel.DataAnnotations;

namespace Server.Models
{
    public sealed class Exam
    {
        public Guid ExamId { get; set; } = Guid.NewGuid();
        public string Institute { get; set; }
        public string Course { get; set; }
        public string CourseManager { get; set; }
        public int Year { get; set; }
        public string Path { get; set; }
        public string? PublicId { get; set; }

        public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
        public DateTime UpdatedAt { get; set; } = DateTime.UtcNow;
        public ICollection<ExamSubmission> Submissions { get; set; } = new List<ExamSubmission>(); // one exam can have multiple submissions.
    }
}
using System.ComponentModel.DataAnnotations;

namespace Server.Models
{
    public sealed class Exam
    {
        public Guid ExamId { get; set; } = Guid.NewGuid();
        public string Institute { get; set; }
        public string Course { get; set; }
        public string CourseManager { get; set; }
        public int Year { get; set; }
        public string Path { get; set; }
        public string? PublicId { get; set; }

        public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
        public DateTime UpdatedAt { get; set; } = DateTime.UtcNow;
        public ICollection<ExamSubmission> Submissions { get; set; } = new List<ExamSubmission>(); // one exam can have multiple submissions.
    }
}
namespace Server.Models {
    public class User {
        public Guid Id { get; set; }      // UUID
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string? Password { get; set; }
        public string Email { get; set; }
        public int Verified { get; set; }
        public string Role { get; set; }
        public DateTime LastOnline { get; set; }
        public DateTime JoinedDate { get; set; }
    }
}
namespace Server.Models {
    public class User {
        public Guid Id { get; set; }      // UUID
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string? Password { get; set; }
        public string Email { get; set; }
        public int Verified { get; set; }
        public string Role { get; set; }
        public DateTime LastOnline { get; set; }
        public DateTime JoinedDate { get; set; }
    }
}
Schreck
Schreck•4d ago
What's the purpose of ExamAnswer? In my opinion, if I would save the answer, then I would persist the Question as well. But that's just my opinion. And what about the ExamSubmissions? Whats the DTO?
Anton
Anton•4d ago
I mean, just make sure each field is only stored once. It's called level 4a or something like that I think, I don't remember. Then denormalize if you need to optimize certain queries to remove a join you just need to make it the most maintainable at the start which basically means no duplicated data that's how I'd go about it

Did you find this page helpful?