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.

asked 04 Jun '10, 16:52

David%20DeRam's gravatar image

David DeRam
21112
accept rate: 0%


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's gravatar image

rbiffl
203357
accept rate: 0%

1

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
Your answer
toggle preview

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

Question tags:

×15

question asked: 04 Jun '10, 16:52

question was seen: 723 times

last updated: 06 Jun '10, 06:35