MySQL – Optimum Way to Store, Update, and Analyze Integer Data for 100+ Attributes

database-designinnodbMySQL

I need to store the count of all individual characters (A-Z, a-z, 0-9, spaces, punctuation marks, all symbols…) that occur in different texts which will be frequently changing.

I will need to update all counts and analyze all of the counts frequently.

Is one table with close to 100 integer columns (one for the count of each character) the most optimum way to store and retrieve this data?

Best Answer

Assumed business rules

As I currently understand your specifications, the following assertions are of prime relevance:

A User inscribes zero-to-many Texts.
A User registers zero-to-many Characters.
A Character is counted in zero-to-many Sums.
A Text contextualizes one-to-many Sums.
A User computes zero-to-many Sums.

Logical data model

And then, from the above enumerated assertions, I have derived the IDEF1X logical data model shown in Figure 1.

Fig. 1. Characters In Texts Preliminary Data Model

As you can see, with this structure you could have a series of Sums related to a specific Character in the context of a particular Text. Instead of performing an UPDATE each time that a new Sum is computed, you just have to INSERT a new row, which would hold the exact point in time that such Sum was calculated.

In this way, you do not need to have a table with about 100 columns, instead, as stated in comments, I suggest using a Character table that stores each character occurrence in an individual row.

I have also added a User entity in order to depict a more complete scenario, since I assume that all the sums (or counts) are computed by a determined user, probably with the aid of an automatic method.

This structure allows you to analyze with ease the trends of the sums that a given Character presents in relation to a particular Text by comparing the set of values contained in the aforementioned Sum series.

Related Solutions

Mysql – Database Design: What is the best structure for storing site offices and locations

Here is some vanilla SQL:

CREATE TABLE RoomTypes
(
 RoomType VARCHAR(12) NOT NULL,
 UNIQUE (RoomType)
);

CREATE TABLE Zones
(
 Zone VARCHAR(10) NOT NULL,
 UNIQUE (Zone)
);

CREATE TABLE Rooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 UNIQUE (RoomType, Zone, Name), 
 FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
 FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);

CREATE TABLE NumberedRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Numbered'),
 UNIQUE (Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

CREATE TABLE AncillaryRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Ancillary'),
 UNIQUE (Zone, Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.

The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.

Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.

Mysql – How to make multiple counts in one query

To get a count for each of those you can try

SELECT
    COUNT(CASE WHEN `col1` LIKE '%something%' THEN 1 END) AS count1,
    COUNT(CASE WHEN `col1` LIKE '%another%' THEN 1 END) AS count2,
    COUNT(CASE WHEN `col1` LIKE '%word%' THEN 1 END) AS count3
FROM `table1`;