S
S
stationfuk2016-02-14 13:43:01
Database design
stationfuk, 2016-02-14 13:43:01

How to properly organize a database?

Good afternoon.
I am developing a system of online tests on ASP MVC 5, in connection with which I arose on the organization of the database (EF6).
The system has a test in which there are questions. Questions can be edited, removed from the test, etc.
Situation 1: A test was issued in state A to students, after which it was edited to state B. As a result, links in the test results leading to A become irrelevant.
Situation 2: we delete the question from the passed test. We cannot remove it from the database, because then the results will be incorrect, but we also cannot remove them from the list of questions, because then connection with the test is lost.
As a solution, I propose to bind all entities to time (i.e. the link in the test results will be to the question with Id=XXX and modification time=YYY). But how to be with removal - I do not understand.
Please advise what can be done here.

public class ExerciseSet
    {
        [Key]
        public string Id { get; set; } = Guid.NewGuid().ToString();

        [Display(Name = "Название")]
        public string Name { get; set; }
        public string Owner { get; set; }

        public virtual List<ExerciseBlock> ExercisesBlocks { get; set; } = new List<ExerciseBlock>();
        public virtual List<GeneratedVariant> GeneratedVariants { get; set; } = new List<GeneratedVariant>();

        [Display(Name = "Примечания")]
        public string Notes { get; set; }

        public ExerciseSet()
        {
        }

        public ExerciseSet(string ownerId)
        {
            Owner = ownerId;
        }

        [NotMapped]
        [Display(Name = "Кол-во заданий")]
        public int ExercisesCount => ExercisesBlocks.SelectMany(x => x.Exercises).Count();
    }

public class ExerciseBlock
    {
        [Key]
        public int Id { get; set; }

        [Display(Name = "Название")]
        public string Name { get; set; }

        public virtual ExerciseSet ExerciseSet {get; set; }

        public virtual List<Exercise> Exercises { get; set; }
    }
public class Exercise
    {
        [Key]
        [HiddenInput(DisplayValue = false)]
        public string Id { get; set; }

        public virtual ExerciseBlock ExerciseBlock { get; set; }

        /// <summary>
        /// Список блоков вопроса, блоки выводятся в соотв. с порядком
        /// </summary>
        [Display(Name = "Блоки вопроса")]
        public virtual IList<Resource> AskBlocks { get; set; }
        /// <summary>
        /// Ответ на вопрос
        /// </summary>
        [Display(Name = "Варианты ответа")]
        public virtual IList<ExerciseAnswer> Answers { get; set; }
        public string AnswerType { get; set; }
        /// <summary>
        /// Подсказка для режима Обучения
        /// </summary>
        [Display(Name = "Подсказка (Обучение)")]
        public string Hint { get;set; }
        /// <summary>
        /// Id тестируемого предмета
        /// </summary>
        [Display(Name = "Предмет")]
        public string Subject { get; set; }
        /// <summary>
        /// Id тестируемой темы
        /// </summary>
        [Display(Name = "Тема")]
        public string Topic { get; set; } = "";

        /// <summary>
        /// Id тестируемого класса
        /// </summary>
        [Display(Name = "Класс")]
        public string Class { get; set; } = "";
        /// <summary>
        /// Id используемого кодификатора
        /// </summary>
        [Display(Name = "Кодификатор")]
        public virtual Codificator Codificator { get; set; }
        public int? CodificatorId { get; set; }
        /// <summary>
        /// Номер проверяемого элемента по кодификатору
        /// </summary>
        [Display(Name = "Элемент кодификатора")]
        public virtual CodificatorSection CodificatorElement { get; set; }
        public int? CodificatorElementId { get; set; }
        /// <summary>
        /// Номер требования к подготовке по кодификатору.
        /// </summary>
        [Display(Name = "Требование кодификатора")]
        public virtual CodificatorSection CodificatorRequirement { get; set; }
        public int? CodificatorRequirementId { get; set; }
        ///// <summary>
        ///// Сложность (базовая, повышенная, высокая)
        ///// </summary>
        //[Display(Name = "Сложность")]
        //public ExerciseBlock Difficulty { get; set; }
        /// <summary>
        /// Время выполнения задания
        /// </summary>
        [Display(Name = "Время на выполнение задания")]
        public TimeSpan Time { get; set; }
        /// <summary>
        /// Кол-во баллов за выполнение задания
        /// </summary>
        [Display(Name = "Количество баллов")]
        public int Points { get; set; }
        /// <summary>
        /// Заметка к заданию
        /// </summary>
        [Display(Name = "Примечания")]
        public string Notes { get; set; }

        public Exercise()
        {
            Id = Guid.NewGuid().ToString();
            AskBlocks = new List<Resource>();
            Answers = new List<ExerciseAnswer>();
        }
    }

Answer the question

In order to leave comments, you need to log in

1 answer(s)
S
sim3x, 2016-02-14
@sim3x

Тест:
  название
  
ВерсияТеста
  тест = ForeignKey(Тест)
  текст
  дата создания
  автор
  
ВопросТеста
   тест = ForeignKey(ВерсияТеста)
   текст вопроса

ОтветНаВопроТеста
  вопрос теста = ForeignKey(ВопросТеста)

When you create a new version, you suggest copying the TestQuestion from the previous version

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question