Does full text search make sense in the context where there are many columns?

I have a table with user defined columns (the table can be extended to many columns). It would be great if a user could search for 'Ayn Rand' and get rows that match for any column containing '%Ayn Rand%'.

My hope is that full text search gives me a better solution than this...

WHERE col1 like '%Ayn Rand%' OR col2 like '%Ayn Rand%' OR col3 like '%Ayn Rand%'

The perfect situation would be...

WHERE CONTAINS '"Ayn Rand"'

I'm guessing it will require too much text index refreshing and maintenance to be useful but I'm clinging to hope.

Thank you for any insights.

full-text-search

asked 04 Jun '10, 16:52

David DeRam
21●1●1●2
accept rate: 0%

flat view

One Answer:

active answers oldest answers newest answers popular answers

You should look at your table structures. Instead of letting users define their own columns, consider letting them define types of records. They would store all the records, of various user-named types, in a table with the data in one column and an indicator of the record type in another column. The list of user-named record types would be in a separate table.

permanent link

answered 06 Jun '10, 06:35

rbiffl
203●3●5●7
accept rate: 0%

David... I hate to question anyone else's database design, but you have piqued my curiosity on this one. In my mind, the DB designer defines the table & its columns, and the application & user will generally define the rows. How did you get to a design where the users define the columns?
And please accept my apologies for not addressing your question.)

(06 Jun '10, 11:33) Ron Hiner

Not the first time we've heard this. That structure in inefficient for our purposes. It's not a traditional application but more of a tool.

(10 Jun '10, 16:44) David DeRam

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

full-text-search ×15

question asked: 04 Jun '10, 16:52

question was seen: 1,730 times

last updated: 06 Jun '10, 06:35

SAP SQL Anywhere

Does full text search make sense in the context where there are many columns?

Follow this question

Related questions