confident

Improving client-side code with thinner database tables

A friend of mine was telling me about the tons of code he has to write to deal with a Company table which has dozens of parameter columns. I suggested that he use a Parameter table, and then replace all of the parameter columns with a many-to-many table to set the Parameter values for each Company. A lot of the parameters are essentially enumerations, (each can only be one of several values), and that logic can be maintained in the database, as well, (but not with literal ENUM's).

Then, since I was taking Cat Lady to get her hair cut, I decided that, while I was waiting for her, I might as well sketch out a proposed outline of how this whole approach could look in MySQL. To wit:

CREATE TABLE Company (
	CompanyID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
	# Now need far fewer columns here!
) ENGINE = InnoDB;

CREATE TABLE ParameterType (
	ParameterTypeID VARCHAR(8) NOT NULL PRIMARY KEY
		# e.g., "INTEGER", "DATE", "BOOLEAN", "STRING", "ENUMERAT", "FLOAT", etc.
) ENGINE = InnoDB, COMMENT = 'Basic data types allowed for Parameters.';

CREATE TABLE Parameter (
	ParameterID VARCHAR(8) NOT NULL PRIMARY KEY,
		# e.g., "STRUCTUR", "STATUS", "LISTED", etc.
	ParameterTypeID VARCHAR(8) NOT NULL,
	CONSTRAINT ParameterParameterTypeIDFK FOREIGN KEY (ParameterTypeID) 
		REFERENCES ParameterType(ParameterTypeID)
		ON DELETE RESTRICT ON UPDATE CASCADE,
	SortOrder SMALLINT UNSIGNED NOT NULL UNIQUE KEY 
		COMMENT 'Presentation order',
		# Recommend assign by thousands, e.g., 1000, 2000, etc.
	Description VARCHAR(30) NOT NULL
) ENGINE = InnoDB, COMMENT = 'Parameters which describe Companies.';

CREATE TABLE ParameterValue (
	ParameterID VARCHAR(8) NOT NULL,
	CONSTRAINT ParameterValueParameterIDFK FOREIGN KEY (ParameterID) 
		REFERENCES Parameter(ParameterID)
		ON DELETE RESTRICT ON UPDATE CASCADE,
	ParameterValueID VARCHAR(8) NOT NULL,
		# e.g., For "STRUCTUR": "PRIVATE", "PUBLIC", "LLP", etc.
	PRIMARY KEY (ParameterID, ParameterValueID),
	SortOrder SMALLINT UNSIGNED NOT NULL UNIQUE KEY 
		COMMENT 'Presentation order',
		# Recommend assign by tens, e.g., For "STRUCTUR": 1010, 1020, etc.
	Description VARCHAR(30) NOT NULL
) ENGINE = InnoDB, COMMENT = 'Allowed values for enumerated Parameters.';

CREATE TABLE Attribute (
	CompanyID INT UNSIGNED NOT NULL,
	CONSTRAINT AttributeCompanyIDFK FOREIGN KEY (CompanyID) 
		REFERENCES Company(CompanyID)
		ON DELETE RESTRICT ON UPDATE CASCADE,
	ParameterID VARCHAR(8) NOT NULL,
	CONSTRAINT AttributeParameterIDFK FOREIGN KEY (ParameterID) 
		REFERENCES Parameter(ParameterID)
		ON DELETE RESTRICT ON UPDATE CASCADE,
	PRIMARY KEY (CompanyID, ParameterID),
	ValueInteger INT NULL DEFAULT NULL,
		# For use with "INTEGER", "DATE", "BOOLEAN", etc.
	ValueString VARCHAR(255) NULL DEFAULT NULL,
		# For use with "STRING", "ENUMERAT", etc.
	ValueFloat FLOAT NULL DEFAULT NULL
		# For use with "FLOAT", etc.
) ENGINE = InnoDB, COMMENT = 'Association between Companies and Parameters.';
confident

My tweets

  • Tue, 17:53: 3rd attempt: arr band nat pk ok. Main loop closd. Takg Frey trail only 2 rim, no more. 2Mi. 100ft El chg. 1.5h. Lv park by sunset.
  • Tue, 18:58: Back @ trail head. Did get 2 rim. Saw 4 mule deer. Heavy snow mid-hike. Clear now. Will fill tank @ white rock.
  • Tue, 19:35: Back safe in los Alamos.
  • Tue, 20:12: So, good news is I was able 2 get 2 Bandelier Nat Mnmt after all, albeit solo. Couldn't c the main sights, but still found great challenges.
  • Tue, 22:37: Roller Derby practice w Sara, Anne & Betty.
  • Wed, 05:55: 4am: LAM & ABQ have no snow. ATL & AVP both have rainstorms, but those can be flown around, so perhaps minor delays @ worst. Should b fine.
  • Wed, 09:20: From final pack 2 checked in @ LAM in 10 min; impressive!
  • Wed, 10:20: Arr ABQ early. Start 5h layover w Sara's recommendation of blue corn pancakes @ Tia Juanita's.
  • Wed, 10:55: Mouse bat dead. AA?
  • Wed, 14:23: The ABQ "PET ME" dog is greeting every1. Leaving 4 ATL soon.
confident

My tweets

  • Wed, 04:30: AVP
  • Wed, 04:57: TSA usual pain. Glad early; zoo.
  • Wed, 08:22: ATL. Early. Slept whole 2h flight. Good start 2 19h travel day. Very bright am.
  • Wed, 08:57: Free wi-fi @ ATL, but insecure & 5Mbps, so can't work, email, etc.
  • Wed, 10:05: Looks like next flight ATL > ABQ is also running on time. Boarding soon.
  • Wed, 14:10: ABQ. Slept maybe an hour, then alternated reading & watching out the window.
  • Wed, 15:37: An hour later, my ride's still not here; not answering cell, txt, email.
  • Wed, 17:31: Decided 2 rent car.
  • Wed, 18:37: Finally reached Sandia Peak 10,300'. Feldspar. Pieces of 1955 crashed plane.
  • Wed, 20:36: Between traffic & getting horribly lost, I made it 2 Petroglyph Nat'l Mon't JUST as it got so dark that I couldn't c a thing!! (Cry)
Collapse )