MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE abbrv (
    abbrv_id int unsigned primary key not null auto_increment,
    usps_primary varchar(64) not null,
    usps_preferred varchar(16) not null
);

CREATE TABLE abbrv_variation (
    variation_id int unsigned primary key not null auto_increment,
    abbrv_id int unsigned, -- FK
    variation varchar(64) not null
);

INSERT INTO abbrv(usps_primary, usps_preferred) VALUES('North', 'N');
INSERT INTO abbrv(usps_primary, usps_preferred) VALUES('South', 'S');
INSERT INTO abbrv(usps_primary, usps_preferred) VALUES('East', 'E');
INSERT INTO abbrv(usps_primary, usps_preferred) VALUES('West', 'W');

INSERT INTO `abbrv` (usps_primary, usps_preferred) values('ALLEY', 'ALY');
SET @abbrvId = LAST_INSERT_ID();
INSERT INTO `abbrv_variation` (abbrv_id, variation) VALUES(@abbrvId, 'ALLEE');
INSERT INTO `abbrv_variation` (abbrv_id, variation) VALUES(@abbrvId, 'ALLY');

INSERT INTO `abbrv` (usps_primary, usps_preferred) values('ANEX', 'ANX');
SET @abbrvId = LAST_INSERT_ID();
INSERT INTO `abbrv_variation` (abbrv_id, variation) VALUES(@abbrvId, 'ANNEX');
INSERT INTO `abbrv_variation` (abbrv_id, variation) VALUES(@abbrvId, 'ANNX');
INSERT INTO `abbrv_variation` (abbrv_id, variation) VALUES(@abbrvId, 'ANX');

usps_primary	usps_preferred	variation
North	N	North
North	N	N
South	S	South
South	S	S
East	E	East
East	E	E
West	W	West
West	W	W
ALLEY	ALY	ALLEY
ALLEY	ALY	ALY
ALLEY	ALY	ALLEE
ALLEY	ALY	ALLY
ANEX	ANX	ANEX
ANEX	ANX	ANX
ANEX	ANX	ANNEX
ANEX	ANX	ANNX