Reputation: 5
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
Reputation: 15971
The following is for the most part personal preferences developed over almost 10 years of working with databases (MySQL mostly).
TINYTEXT
, MEDIUMTEXT
, TEXT
, etc... might be a better option (or equivalent BLOB
s). 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