0

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.

Improve this question
4
  • one thing to keep in mind in regards storing both upper and lower case variants will be dependent on the web server type. For instance Apache servers in general are case sensitive so example.com is a different URL to Example.com – sdjuan Oct 23 '12 at 20:27
  • @sdjuan: example.com and Example.com must be the same: en.wikipedia.org/wiki/URL_normalization - at least they are the same for Google what only interests me – porton Oct 23 '12 at 20:35
  • then clearly you do not need to store both :) – sdjuan Oct 24 '12 at 16:36
  • ahh my bad, to correct what I said earlier, it's the part after the scheme and host that is case sensitive with Apache, so example.com/thisOne can be different from example.com/thisone but not apparently in your case. – sdjuan Oct 24 '12 at 16:44
1

I decided to do it in the following way:

  • Make a table strings with an ID and a VARCHAR, which will store both URLs and keywords, both normalized forms and unmodified user input.

  • Make a table seo which will hold (in addition to the row ID) four numeric fields referring to strings: unmodified user input for URLs and keywords, normalized URLs and keywords.

The list of normalized URL/keyword pair will be generated on the fly using DISTINCT.

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.