Improving client-side code with thinner database tables

[d4b]

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.';