Populating an array(?) from a DB field containing multiple values 1

[GastroScout]

Hi,
First off: my site -

http://www.gastroscout.com

- runs CF 5/SQL server 7.0, and has around 24,000 records (restaurants in Germany, Austria, Switzerland)

For historical reasons, the "Cuisine" field is defined as a simple text (nvarchar) field that contains multiple, comma/blank-delimited values for each restaurant (i.e. "international, German, French&quot. What I want to do is - for any given selection - populate an HTML Select dropdown containing all the cuisine values for the given selection, with no duplicates.

I can't use SELECT DISTINCT because the field contains multiple values...

Specific example: the selection results in the following hits:
RestaurantNumber Cuisine
1 international
2 international, German
3 French, Italian
4 Italian, German
5 American, German, international

The dropdown, which would be used to narrow the selection in a follow-on search, should look like this:
- American
- French
- German
- international
- Italian

Is there any way to do this with the existing structure or do I have to create a new structure (i.e. nested table) and copy the data?

Thanks in advance for your help,
Jeff Zalkind
Editor-in-Chief and Webmaster, GastroScout.com

 

[csteinhilber]

You could do something like:

<!--- initialize a local list to null --->
<CFSET myCuisines = &quot;&quot;>
<CFLOOP query=&quot;restaurants&quot;>
   <!--- loop over each record --->
   <CFLOOP index=&quot;whichCuisine&quot; list=&quot;#restaurants.cuisine#&quot;>
       <!--- loop over each item (comma-separated) in the cuisine field for that record --->
       <CFIF ListFindNoCase(&quot;#myCuisines#&quot;,trim(whichCuisine)) LT 1>
           <!--- if the local list doesn't currently contain the given cuisine, add it --->
           <CFSET myCuisines = ListAppend(&quot;#myCuisines#&quot;,trim(whichCuisine))>
       </CFIF>
   </CFLOOP>
</CFLOOP>

<!--- sort the list --->
<CFSET myCuisines = ListSort(&quot;#myCuisines#&quot;,&quot;Textnocase&quot;)>

<SELECT name=&quot;cuisinesDropdown&quot; id=&quot;cuisinesDropdown&quot;>
<!--- build the dropdown from the local list --->
<CFLOOP index=&quot;dropdownitem&quot; list=&quot;#myCuisines#&quot;>
    <CFOUTPUT><OPTION value=&quot;#lcase(dropdownitem)#&quot;>#dropdownitem#</OPTION></CFOUTPUT>
</CFLOOP>
</SELECT>

Or something like that.
Hope it helps,
-Carl

 

[SidianConsulting]

Normalize your database, so you have the restaurant number and cuisine in a separate link table, then join the restaurant table to the link table on the restaurant number, which would be a one (restaurant table) to many (link table) join. Create a clustered composite primary key (meaning both fields together would make up the primary key) on the link table fields for speed.

The trade-off here is that you'd have to perform a table join when outputting a restaurant's cuisine -- you'll have to decide if parsing the database column with CF is faster than performing this join (not likely in my opinion). I don't think I ever put comma-delimited data in a database field, unless I was creating denormalized tables for a data warehouse, archive or OLAP. It's just not good practice in most situations.

-Tek

 

[GastroScout]

@csteinhilber: Fantastic response! I haven't had a chance to implement it yet but it looks very promising.

@Teknology: Thanks for the techno-babble and the scolding ("just not good practice in most situations&quot. Didn't I say the data format has historical reasons? Aside from a poke in the eye with a sharp stick, I can't think of anything less helpful than your comments.

Jeff

 

[csteinhilber]

Hey Jeff,
First, I'm glad you thought my solution holds promise.

However, do kindly refrain from the veiled insults in these forums. Tek is a good guy and a top-notch CFML slinger. Not only that, but he was 100% correct in everything he said.

If you didn't find that his comments were of value for your particular situation, a "thanks Tek, but that won't work for me in this case" would be a far more appropriate response (or simply don't respond at all).

Folks that respond to other's pleas on Tek-tips (and Tek does quite regularly) do so because we believe in sharing knowledge and experience. We do so even though we have everyday jobs and lives, and all things being relative, in actuality, probably do not have the spare moments it takes to even browse these forums. The fact that responses might not take into account every single point made in the original post is a sign that we're stepping rather quickly.

Above all, remember that you're probably going to have other questions that you'll need to post here or in the other tek-tips forums from time to time. If you continue to berate folks who are only trying their best to help, you'll find fewer and fewer people willing to do so.
Hope it helps,
-Carl

 

[SidianConsulting]

Carl did a great job working around a de-normalized database. Unfortunately, it's something all too common. While my response could be construed as "techno-babble" for someone who doesn't understand it, I strived to be clear and concise in my response.

My response is based on what I'd do, based on my experience. I could've given you code to make it work, but I thought I'd give you a solution that would not create more problems down the road. It was hardly a scolding -- it is definitely a best practice to normalize databases.

From what I can see of your application so far, having the database do most of the work would be better than doing it programmatically within ColdFusion.

I'm sorry if you took my response personally, it wasn't meant to be taken that way. Maybe I should've stuck some happy faces in there

-Tek

 

[iamkillyou]

I do sort of what tek posted on my site.

http://www.barsmart.com

I have Bartypes table and an nBartypes table. The BarTypes table stores the location id of the bar, the bartypeid of the type of bar it is (which can be many) and the primary key is just an autonumber.

The nBarTypes table has an nbartypeid which is the id of the type inserted into the bartypes table and a text field for the typename. What I don't quite get is the clustered composite key that he mentioned?