0
  • Task: Database Revision
  • Legacy System: LAMP (MySql v5.5)
  • Current System: Node Express MySql v8.023

Data is acquired from 14 sources as .txt files with different delimiters and imported to their own child table. Only SELECT statements are used to query data.

Each of the 14 child tables have:

Associated data type that are normally not an INT; Between 8 and 92 columns of data. Between 4 and 24 foreign key relationships with parent tables normally not using an INT for PRIMARY KEY.

In Legacy version PhpMyAdmin was used allowing display of data from parent table when using simple SELECT statement on child table with foreign key.

Current development team anticipates problems with not using INT for PRIMARY KEY

Not sure if current configuration would be considered a staging environment and how to determine best practices to enhance to a production environment?

Any explanation or suggestion research topics are appreciated.

Example

Table_1 stores data including address information that is imported from fixed width .txt document.

CREATE TABLE table_1 (
strno INT NULL,
strfrac VARCHAR(255) NULL,
strdir VARCHAR(255) NULL,
strname VARCHAR(255) NULL,
strtype VARCHAR(255) NULL,
strunit VARCHAR(255) NULL,
city VARCHAR(255) NULL

3 of the 8 columns require translation before delivery to user including:

strdir strtype city

For these 3 columns 3 separate tables have been created to include:

  • Code for all possible values in table_1;
  • Translated name for display to User;
  • Description for display to User;

table strdirs records

'N','North','North is a valid direction'
'S','South','South is a valid direction'
'E','East','East is a valid direction'
'W','West','West is a valid direction'
'NE','Northeast','Northeast is a valid direction'
'NW','Northwest','Northwest is a valid direction'
'SE','Southeast','Southeast is a valid direction'
'SW','Southwest','Southwest is a valid direction'

table strtypes records

'ALLY','Alley','An Alley is a valid street type for a location address'
'ANX','Annex','An Annex is a valid street type for a location address'
'AVE','Avenue','An Avenue is a valid street type for a location address'
'BEND','Bend','A Bend is a valid street type for a location address'
'BLVD','Boulevard','A Boulevard is a valid street type for a location address'
'BYP','Bypass','A Bypass is a valid street type for a location address'
'CYN','Canyon','A Canyon is a valid street type for a location address'
'CIR','Circle','A Circle is a valid street type for a location address'

table cities records

'BC','Boulder City','Boulder City is a beautiful city in southern Nevada'
'BUNK','Bunkerville','Bunkerville is a beautiful city in southern Nevada'
'CNTY','County','County is a beautiful city in southern Nevada'
'ENT','Enterprise','Enterprise is a beautiful city in southern Nevada'
'GLEN','Glendale','Glendale is a beautiful city in southern Nevada'
'HEND','Henderson','Henderson is a beautiful city in southern Nevada'
'ISPR','Indian Springs','Indian Springs is a beautiful city in southern Nevada'
'LAUG','Laughlin','Laughlin is a beautiful city in southern Nevada'

Table_1 has foreign key relationships with 3 tables

strdirs - ALTER TABLE table_1 ADD FOREIGN KEY (strdir) REFERENCES strdirs(id); strtypes - ALTER TABLE table_1 ADD FOREIGN KEY (strtype) REFERENCES strtypes(id); cities - ALTER TABLE table_1 ADD FOREIGN KEY (city) REFERENCES cities(id);

By 'staging environment' I was picturing a preliminary environment used to get all data into one place and then transform that data using the parent tables and load into a new productions environment. It would be great if the most efficient methods could be determined for converting the 'N' in table_1 to 'North' or 'North is a valid street direction' (the values in strdirs table) Some of the other parent table codes are less obviously related to the value contained in table_1 or other child tables. The values in the parent tables have been constant over the last decade and not likely to require edits.

The first column is the primary key. I can include the create table and insert statements if it would help. I will go back through and update the size max. Previously it looked like a safe size for matching relationships and thought VARCHAR only reserved the amount of space actually used so thought it wouldn't have negative affect.

2
  • Thanks for the edit! – Adam Wyatt Mar 31 at 22:12
  • Not sure what you mean exactly by making it a staging environment in this context. Not having an INT as the primary key isn't necessarily bad but can become an issue if your primary key / indexed fields are large data types, for example if your index for table_1 was on (strfrac, strdir, strname), a 765 byte index per row, then that could cause some noticeable performance issues. Anyway you should add some specific details to help us focus in on your performance issues, such as some example slow queries, their average run times, and the EXPLAIN ANALYZE output of them. – J.D. Apr 1 at 0:56
0

You showed sample data for 3 tables. Is the first column the PRIMARY KEY? Is it unique? Then it is perfectly fine as the PK. In fact, it is probably better than adding a surrogate auto_increment. Yes, Foreign Keys are happy to use VARCHARs.

But, please, don't blindly use (255); pick some sensible max. (In a list of 3M cities around the world, this 91-char Russian city is the longest: "Poselok Uchebnogo Khozyaystva Srednego Professionalno-Tekhnicheskoye Uchilishche Nomer Odin". For the US, I get the 26-character "Los Ranchos de Albuquerque")

A pet peeve of mine: Drop the common "str" prefix; it clutters.

I need more details to understand your title question.

3
  • Thanks J.D. and Rick James, I updated the question. – Adam Wyatt Apr 2 at 15:45
  • @AdamWyatt - In most situations, (255) is not really a problem. In a few situation they can slow down complex queries, but 8.0 is possibly fixing that. – Rick James Apr 2 at 17:26
  • @AdamWyatt - I have seen a USPS site that will clean up addresses (N vs North, etc) and append 9-digit zips. Sorry, I don't have a link to it. "Staging" -- good. Cleanse the data before INSERTing. – Rick James Apr 2 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.