I need:
- a table containing keyword phrases;
- a table containing URLs;
- a table (or maybe a view) with keyword-URL pairs (referring to IDs of the two previous tables);
- tables holding keywords/URLs for particular users.
The trouble is that both URLs and keywords may have several different forms. For example, both keywords and domains in URLs may be lowercase of uppercase, or http://example.com is the same as http://example.com/ (with trailing slash).
The question is how to manage user input when several possible variants are possible.
For now I can't formulate this question more exactly, as I have not formulated the exact specification of what I need. You are free to interpret my question in any way you may do it.
The sub-questions of this question:
- Should I store both uppercase/lowercase variants or just the exact user input, generating my upper case version on the fly? (I need these "standardized" lowercase strings when doing actual URL queries from our server. It looks like for me that it's better to store them in the DB rather than to generate on the fly, for performance reason).
- Should I use stored procedures to overcome such a deceiving DB layout, with that mess of lowercase and uppercase strings?
It is OK to use transactions. The DB is in MySQL.