我的MySQL架构是否有意义？有什么建议？

I need to create a database that stores the following content (about 5,765 entries total): http://s18.postimg.org/s73exwemf/Capture.jpg

我需要创建一个存储以下内容的数据库(总共约5,765个条目):http://s18.postimg.org/s73exwemf/Capture.jpg

I'm using MySQL Workbench to create my schema. So far I have one table with the following columns:

我正在使用MySQL Workbench来创建我的架构。到目前为止,我有一个包含以下列的表:

EPSG Code (INT) - PK, NN

EPSG代码(INT) - PK,NN

CRS_NAME CHAR(50) - UQ

CRS_NAME CHAR(50) - UQ

CRS_TYPE - ENUM('Projected', 'Geographic 2D', 'Geographic 3D', 'Geocentric', 'Vertical', 'Compound')

CRS_TYPE - ENUM('预计','地理2D','地理3D','地心','垂直','复合')

PROJ_FILE - CHAR(800)

PROJ_FILE - CHAR(800)

---

Do my dataypes make sense? Generally, I will retrieve the CRS Name, type and proj file contents using the EPSG code. But sometimes, the only information available may be the CRS name. That's why I made CRS_NAME a unique index.

我的数据表有意义吗?通常,我将使用EPSG代码检索CRS名称,类型和proj文件内容。但有时,唯一可用的信息可能是CRS名称。这就是为什么我让CRS_NAME成为一个独特的索引。

Does that make sense? I'm new to SQL and I'm enjoying it so far.

那有意义吗?我是SQL的新手,到目前为止我很享受它。

1 个解决方案

#1

The following is for the most part personal preferences developed over almost 10 years of working with databases (MySQL mostly).

以下是大多数人在使用数据库(主要是MySQL)近10年后开发的个人偏好。

• CRS_NAME: Unique key sounds appropriate to me.

CRS_NAME:独特的关键声音对我来说很合适。

• CRS_TYPE: I tend to stay away from enum in the database. Instead, I suggest a separate CRS_TYPE table, and putting a CRS_TYPE_ID field in the table instead of an enum type. I would not make CRS_TYPE.ID an auto-increment; ideally, you want it to reflect the values used in an enum in whatever programming language you might work with. (Technically, the additional table is only necessary for documentation and easier reporting purposes.)

CRS_TYPE:我倾向于远离数据库中的枚举。相反,我建议使用单独的CRS_TYPE表,并在表中放置CRS_TYPE_ID字段而不是枚举类型。我不会让CRS_TYPE.ID成为自动增量;理想情况下,您希望它以您可能使用的任何编程语言反映枚举中使用的值。 (从技术上讲,附加表仅用于文档和更简单的报告目的。)

• PROJ_FILE: TINYTEXT, MEDIUMTEXT, TEXT, etc... might be a better option (or equivalent BLOBs). CHAR(800) is going to use 800 (or more depending on character set) bytes whether it holds nothing or is full. VARCHAR(800) could be better (from a space used perspective), but if using MyISAM engine, causes data rows to be dynamic (slowing queries). Regardless of engine used, TEXT and BLOB types only take up as much room as they need and don't "fragment" tables like VARCHAR. The downside is they are little more complicated to search within and index.

PROJ_FILE:TINYTEXT,MEDIUMTEXT,TEXT等......可能是更好的选择(或等效的BLOB)。 CHAR(800)将使用800(或更多取决于字符集)字节,无论它什么都不存在或已满。 VARCHAR(800)可能更好(从空间使用的角度来看),但如果使用MyISAM引擎,则会导致数据行动态(减慢查询)。无论使用何种引擎,TEXT和BLOB类型只占用他们需要的空间,并且不像VARCHAR那样“分段”表。缺点是它们在内部搜索和索引更复杂一些。