If the system starts growing more complex, or slows down due to waiting on access to that one big table, learn you some relational database theory. But, as long as it's fairly simple, and not too big, there's little to gain by making it more complicated, when there aren't real gains from it. As long as the data stays sufficiently simple, it will also be fairly easy to convert it into a proper relational DB (or non-relational big object store), should the need arise. When value types start relying on values from other columns, or you need dozens of columns in a single table, that's usually when you're getting into the flat file death spiral
🙂.
For example, you'll likely have many context duplicate entries. These can often be sped up by making an enum table, listing the contexts, os the contexts are 1-, 2-, or 4-byte ints, rather than varchars. The result is a smaller, and usually faster, main table, but requiring a separate table to keep up with and join to.
Code:
contexts {
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
context VARCHAR(255) NOT NULL UNIQUE
}
main {
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_context int FOREIGN KEY REFERENCES CONTEXTS(id),
keyword varchar(255)
}
Code:
SELECT id, context, keyword FROM MAIN
INNER JOIN CONTEXTS ON main.id_context=contexts.id
Where the join is really just filling in the name of the context, for that query. It can be faster when looking up some contexts, in that you can fill in the context names, and then it can look for the integer id of that in the main table (even though you gave it strings), rather than string matching, which takes more time.
But, so long as you don't do any bad flat file stuff, like changing column value types based on other data, that kind of thing can be relatively easily done later, if needed (you could even write triggers to automatically manage it, with new base tables, so that users--
including existing application code--won't have to even know that's going on).
For iterating over all of them, you'll probably just want to order the results, and loop through them with a current context state variable, or inner loop that you break out of (to re-enter with the new context value):
Code:
SELECT * FROM MAIN ORDER BY CONTEXT, PK ASC
For what matter, the same could be added to a smaller listing of them, too:
Code:
SELECT * FROM MAIN
WHERE CONTEXT IN('main', 'context1')
ORDER BY CONTEXT, PK ASC
Then, you can either treat it flat, or loop over it to add it to a set of arrays. A two-query straight-forward approach, where the first query is used to build that first level of container arrays, would be something like this combo:
Code:
SELECT DISTINCT CONTEXT FROM MAIN ORDER BY CONTEXT ASC;
SELECT PK, KEYWORD, CONTEXT, DATA FROM MAIN
ORDER BY CONTEXT, PK ASC;
Then do a for loop over the list of contexts, to put them in arrays named for each context.
Alternatively, while it may take more work than obvious, depending on DBMS, you could use aggregate functions. However, concatenating ones vary by implementation, including their limits (MySQL's group_concat, FI, requires a higher limit set, usually, as it defaults to a max of 1KB per result row).
