Would current configuration be better as a staging environment and how to determine best practices to enhance production environment

Question

• 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.

Answer 1

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.