CODEWITHSUNDEEP
mysqlsql
c47v3770
c47v3770

Reputation: 5

Does my MySQL schema make sense? Any recommendations?

I need to create a database that stores the following content (about 5,765 entries total): 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:

EPSG Code (INT) - PK, NN

CRS_NAME CHAR(50) - UQ

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

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.

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

Upvotes: 0

Views: 67

Answers (1)

Uueerdo
Uueerdo

Reputation: 15971

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

  • CRS_NAME: Unique key sounds appropriate to me.
  • 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.)
  • 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.

Upvotes: 1

Related Questions