A summary of Jira Database Design

I was bored today. I imported the Jira database (MySQL version) and checked it in navicat. Summarize the following points:

I. Various types:

Tables: N

Views: 0

Functions: 0

Trigger: 0

Ii. Foreign Key Usage:

Foreign keys are rarely used in table design. Except for a set of tables that appear to be automatically generated, there are basically no foreign keys.

There is a lot of debate over the use of foreign keys on the Internet:

Http://www.itpub.net/thread-1313696-1-1.html

Http://www.itpub.net/thread-1312844-1-1.html

Iii. Table data type selection:

Basically only the following types are used

Decimal: 18

Decimal: 9

Varchar (255)

Char (x) as needed

Longtext

Datetime

Iv. Table Name and field naming rules

Mainly in full English, such as author

There are no underlines available for multiple words, such as projectlevel and password_hash. I feel that this one is not very standard.

Each table contains a primary key. Most tables with IDS as the primary key also use multiple primary keys.

Summary:

To be compatible with multiple databases, select as few data types as possible to improve compatibility in various databases and introduce as few database features as possible.

If you have no comments on the foreign key, see the two links.

Id. Generally, decimal (18) is used to guarantee large data volumes (bigint is supported in MySQL, But Oracle is not. For the sake of universality), why is it 18? Corresponding to Java. Lang. Long.

Decimal (18) can be mapped to long (JIRA is written in Java). If it is 19 BITs, it may exceed the maximum number of long 9223372036854775807.

Generally, varchar uses more than varchar (255) and databases. Multi-point loss is better than the length.

The following is a preview: