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?


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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.