CODEWITHSUNDEEP
mysqlsqlajaxdatabasedatabase-design
Karthik Sekar
Karthik Sekar

Reputation: 178

mysql tables redesign ideas (relationships)

I have tables with relationships like the below:

ERD

I have cascading drop down boxes linked to each other, i.e. when you select countries, the regions under that country will be loaded in the regions drop down. But now I want to change the drop downs to Ajax based auto-complete text-boxes.

My question is, how many text-boxes I should have? Should I have one text-box for everything like "search by location", I would need to change table design, or one text-box for each like country, region, city etc,

If I have textboxes like these, the users may not know, few places whether they are region or a city, for example Auckland, New Zealand is a region not city.

They may search for regions in city textbox & search cities in region textbox...now that they have a dropdown, they can see their region from it, "Auckland will be there in region for sure"

I may not find what I want from individual text-boxes,

I need some suggestions on redesigning from both the database & interface point of view.

Upvotes: 0

Views: 208

Answers (1)

hythlodayr
hythlodayr

Reputation: 2387

Your schema is fine. But it sounds like what the user wants at a minimum is: 1. A google-style free-form text field which they can just type in words, but... 2. Which brings up a subset of matching results in a combo-style fashion.

So here's the deal: Search-like capability isn't what relational databases are designed for, and that's basically the problem you're running into. That said, MySQL, while not my domain of expertise, does seem to have reasonable full-text search support (MySQL Full Text Search).

Perhaps you could have FULLTEXT indices on each of the description fields and issue five different queries. Or if you're willing to go with a dirty solution, have a separate BUSINESS_SEARCH(business_id, concat_description) where concat_description is just all of the related "description" fields munged together; though you'll need to account for description updates.

But I have no idea what the performance implications are with FULLTEXT. If it's non-trivial, I'd offload these queries to a separate copy of the server.

My personal feeling--completely without evidence to back it up--is that you'll run into performance problems down the road. Have you considered an add-on? A quick google search-engine shows Google-like Search Engine in PHP/mySQL. The big downside is that you're introducing all of the pitfalls of yet an unproven/unfamiliar technology.

For either approach, I think you have some research cut out for you.

Good luck!

Upvotes: 2

Related Questions