Online Judge (OJ) building--2, database construction

Database EER diagram

Database tables, fields, constraint interpretations

Users:

ID identifier, email address, password password, name name, sex Sex, enabled, role roles

ID PRIMARY Key

Categories Category:

ID identifiers, name names, description description

ID PRIMARY Key

Questions questions:

ID identifier, QID problem number, description description, CID category identifier, name

ID PRIMARY Key

CID--Categories (ID)

Test_data test Data:

ID identifier, QID, input a set of inputs, output a set of outputs

ID PRIMARY Key

QID-Questions (ID)

Tests test information:

ID identifier, UID user identifier, qid problem number, Submit_time commit time, code codes, correct_rate correct rate

ID PRIMARY Key

UID, users (ID)

QID-Questions (ID)

Design Ideas 1, why should each table have an ID, and the ID as the primary key?

The primary key of the table should not be able to be changed, and the actual demand will change. at first, table questions was not listed as CID, and later in order to simulate the category of the real topic (questions) (categories), the CID column was added.

Suppose there is a situation:

The category name (categories name) is Java, the problem number (QID) is three-to-one, the category is C #, and the number is three.

If the table questions is qid as the primary key, the above scenario is not possible because the primary key violates the uniqueness constraint, requires a redesign of the schema, and if the table questions as a primary key with a meaningless ID, the above implementation is simple and does not require a schema change.

Therefore, the primary key of the table is preferably a meaningless ID.

2, table questions and table Test_data design

Table questions initially and Test_data are put together, that is, input and output are initially in table questions, and each record represents multiple sets of input and multiple sets of output. And then I stripped it down, and each record is transformed into a set of inputs and a set of outputs from multiple sets of inputs and sets of outputs for the following reasons:

① multiple sets of input or multiple sets of output is not easy to save. If merged into a set of saves, a symbol must be used as a delimiter, however, in the OJ system, the input of any symbol is possible, the delimiter cannot be selected

② If multiple groups are used to save, the redundancy is high, qid, name and so on have saved many times.

So, I take a weak association (the multi-valued attribute is stripped, a new table is deposited, and the new table is highly dependent on the original table) to save.

Online Judge (OJ) building--2, database construction