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 DeRam |
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. answered 06 Jun '10, 06:35 rbiffl 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? 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. |