MySQL评级数据库结构 [英] MySQL rating database structure

问题描述

我正在尝试创建一个数据库来存储每个家庭作业的学生成绩 我希望能够存储成绩以及该学生获得 certin 成绩的次数，例如学生获得了 A+ 30次数，对于每个学生，以及学生获得的成绩.以及一个等级值多少分，例如 A 值 4.3.

所以我想知道构建 MySQL 数据库的最佳方法是什么，我需要添加什么，需要删除什么，以及如何存储诸如 4.3 之类的数字.>

到目前为止我的数据库

CREATE TABLE 成绩 (id INT UNSIGNED NOT NULL AUTO_INCREMENT,等级 INT UNSIGNED NOT NULL,student_work_id INT UNSIGNED NOT NULL,student_id INT UNSIGNED NOT NULL,date_created DATETIME NOT NULL,主键 (id));创建表工作(id INT UNSIGNED NOT NULL AUTO_INCREMENT,student_id INT UNSIGNED NOT NULL,标题文本非空，内容长文本不为空，主键 (id));如果不存在学生，则创建表(id int(8) NOT NULL auto_increment，学生 varchar(20) NOT NULL,主键(`id`))

输出示例.

学生 |等级 |数数1 101 C 21 楼 42 乙 202 B- 32 C+ 10学生 |等级 |积分1 4.32 B+ 3.33 B- 2.74 D+ 1.3学生 |总成绩1 902 803 1004 1

解决方案

我认为学生 - 年级 - 作业模式应该没问题.您应该通过查询计算学生的成绩数，而不是在数据库中维护该信息.在概念层面(不是严格的 mysql)，我会做这样的事情.

表学生(id int(主键)变量名...//其他学生属性(例如电话号码))表级(id int(主键)名称 varchar//例如A+"、B-"等浮点数//等级值以点为单位)表分配(id int(主键)name varchar//赋值名称student_id int (foreign key)//完成特定作业的学生Grade_id int (foreign key)//在特定作业中取得的成绩...//其他分配属性(例如截止日期))

I'm trying to create a database that stores the students grade for each homework assignment I want to be able to store the grade and how many times that student got a certin grade for example a student got an A+ 30 times, for each student, as well as how many grades a student got. And how much a grade is worth in points for example an A is worth 4.3.

So I was wondering what is the best way to build my MySQL database what do I need to add and what do I need to drop and how can I store numbers like 4.3 for example.

My database so far

CREATE TABLE grades (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
grade INT UNSIGNED NOT NULL,
student_work_id INT UNSIGNED NOT NULL,
student_id INT UNSIGNED NOT NULL,
date_created DATETIME NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE work (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
student_id INT UNSIGNED NOT NULL,
title TEXT NOT NULL,
content LONGTEXT NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS student (
id int(8) NOT NULL auto_increment,
student varchar(20) NOT NULL,
PRIMARY KEY  (`id`)
)

example of output.

student |   grade   |   count

    1       A           10
    1       C           2
    1       F           4
    2       B           20
    2       B-          3
    2       C+          10


student |   grade   |   points

    1       A           4.3
    2       B+          3.3
    3       B-          2.7
    4       D+          1.3


student |   total grades

    1       90
    2       80
    3       100
    4       1

解决方案

I think a student - grade - assignment schema would be ok. You should count the number of grades of a student with a query and not maintain that information in your database. At a conceptual level (not strict mysql), I would do something like this.

table student (
  id int (primary key)
  varchar name
  ...                           // other student attributes (e.g. phone number)
)

table grade (
  id int (primary key)
  name varchar                  // e.g. "A+", "B-", etc.
  float points                  // grade value in points
)

table assignment (
  id int (primary key)
  name varchar                  // assignment name
  student_id int (foreign key)  // student that completed the particular assignment
  grade_id int (foreign key)    // grade achieved in particular assignment
  ...                           // other assignment attributes (e.g. due date)
)

这篇关于MySQL评级数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！