0

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.

Karthik Sekar
  • 180
  • 3
  • 12
  • how about you set a single textbox with like "suburb,city,region,country" input format which you can show in textbox hint for the user to know, then you send over the string to your php, explode it on commas "," converting it to an array, then start from last item and thus look for the country first, when you have your country ID you go on and double condition select your regions on the previous item array input (thus the region) and so on... allthought you can make this faster by doing some checks and guessing on the array's length i.e. if array has only 2 items is likely to be City,Country – Carlo Moretti Aug 04 '12 at 21:54
  • Which countries are you designing for? (eg US has States, UK has Counties etc) – Cups Aug 04 '12 at 21:55
  • I am doing it for NewZealand now, probably Australia afterwards... – Karthik Sekar Aug 04 '12 at 21:57
  • @Onheiron: You mean have one textbox and expect users to enter in that order suburb,city...?? and then i don't get what you mean by double condition & how you conclude if there are 2 items, its only city and country?? could you please be more clear on this... – Karthik Sekar Aug 04 '12 at 22:04
  • You're worried about the _data_ you have. So, where are you getting your data from? Are you making it up (probably a bad idea)? – Ben Aug 04 '12 at 22:08
  • @Ben: "So, where are you getting your data from"...sorry i don't get you here.. I am actually confused on how to query the data and also how to restructure it...there are nearly 850,000 records in the business table with the suburb ids & linked further to other table, but now my client wants to replace the dropdowns with auto complete boxes – Karthik Sekar Aug 04 '12 at 22:13
  • @KarthikSekar, you say you don't know whether a entity is a city or a region, which is why you're concerned about the current data-structure. I'm asking where your data is from so that you're not sure whether something is either. Though, from your comment it seems that it's client supplied. Might it not be worth obtaining a definitive list and then applying that to your client's data? – Ben Aug 04 '12 at 22:16
  • @Ben: Alright..got you, The counties, regions are not client supplied only the businesses data is client supplied, i have created this country-region structure... i am saying from users point of you "they may not know whether it's a city or region "...now that they have a dropdown, they can see their region from it, "Auckland will be there in region for sure and select from it..but if they are given textboxes they may look for regions in city textbox and cities in region textbox" – Karthik Sekar Aug 04 '12 at 22:23
  • First, what is the ideal UI? Second, your tables seem overly normalzed. – hythlodayr Aug 04 '12 at 22:26
  • @hythlodayr: I always thought this is an ideal design for locations, to store the lowermost location like suburb to a table and link it to other locations and thought this design would work for any project, but now facing the trouble.. – Karthik Sekar Aug 04 '12 at 22:36
  • Ignoring the table structure for a moment, what would you like to be able to do? Ideal c – hythlodayr Aug 04 '12 at 22:43
  • @hythlodayr: my client claims that the cascading dropdowns are not user friendly and wants to switch over to autocomplete textboxes.. – Karthik Sekar Aug 04 '12 at 22:45
  • And ultimately, the user wants to zoom in on businesses of interest? – hythlodayr Aug 04 '12 at 22:51
  • Yup to find businesses in an area...it shows up their contact details like a directory.. – Karthik Sekar Aug 04 '12 at 22:53
  • Downvoted -2, could somebody please tell me why?? am new to stackoverflow.. i can't see any '-' rating, also no reason why..same thing happened to my previous question as well, i got +1 vote for that question as well and got 5reps.. – Karthik Sekar Aug 05 '12 at 05:11

1 Answers1

2

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!

Community
  • 1
  • 1
hythlodayr
  • 2,377
  • 15
  • 23
  • By the way, you may want to create a separate question for the UI. – hythlodayr Aug 05 '12 at 03:56
  • I was thinking of indexing of business data before, now as a temporary solution, i am thinking to write a text search query for just the locations with existing structure...I'll post that soon and ask suggestions..thanks – Karthik Sekar Aug 05 '12 at 05:14