ferly_man - 4:11 pm on Sep 14, 2010 (gmt 0)

Wow, i really appreciate you taking the time to lend a hand!

The `region_0`, etc., do not all have values. Typically, up to about `region_5` or so will contain a value on a average. Just to point out, `region_x` vs `region_x_slug` is like so:

I do my queries against `region_x_slug` as it is a stripped version of `region_x`, which is the "pretty/display" version, ie. `region_x` might equal 'Beverly Hills', where `region_x_slug` would then equal 'beverly-hills'.

I am inspired with casting regions to integers for the sake of querying. I am full-out SEO in terms of displaying the region name in the URL, however, ie. /california/beverly-hills/ as opposed to: /?region=1234, for obvious reasons, otherwise, a numeric value system would have been on the forefront of my mind when I began.

As usual though, things start out really small and escalate very, very quickly, and I'll be the first to admit that I was not fully prepared for the growth.

Again, my biggest struggle when I began with this table was the inconsistency of the data. For starters, I currently receive 99% of my entries from an external feed/source. Not all listings have many regions, so I figured I'd just try and cover the maximum number of regions that might ever be available .. big mistake, I see.

Question:

"Currently when someone is adding a listing, do they enter a textual value or select from a list?"

Answer:

They are only able to select from a drop-down, pre-populated list generated from the db. My thoughts were yours exactly in that allowing the user to enter in their own information would only lead to thousands of discrepancies.

I am going to go through everything you've said again and see if I can implement your recommendations without blowing things up. The database will only grow from here on out, so your recommendations are pretty much a must-do.

Thanks again for your time and help!

I'll follow-up shortly with my progress.