I'm looking for advice on what kind of database or table structure to use for storing data that users need to search. Specifically, some fields might need to hold lists of values, like keywords connected to different entries. What would be the best approach to set this up?
5 Answers
It really depends on your needs! Many people use Lucene-based systems like Solr, OpenSearch, or Elasticsearch for this. If you want full-text search, PostgreSQL has built-in support for that, and other database engines can also be adapted for similar functionality.
Honestly, I think the indexing mechanism matters more than the database itself. You might want something like a Generalized Inverted Index (GIN). This will let you have a table with an array column, allowing for efficient searches. For instance, you could have a business table with a tags column, and you can search like this: `SELECT * FROM business WHERE tags @> ARRAY['happy_hour'];` I know PostgreSQL supports GIN.
I'd suggest going with a traditional relational database setup. You can keep your main records in one table and create a separate table for keywords or tags. Then, link them with a join table. This way, each entry can have as many keywords as needed, and you can search through everything easily without cramming lists into a single field.
‘Searchable’ can mean different things. Are you looking for faceted search, semantic search, or something else? If your dataset is small and your search needs are limited, a basic PostgreSQL setup can work fine. But for larger datasets, you might want to consider an index database like OpenSearch, especially if you're layering in vector capabilities later on for performance and cost efficiency.
If your searching needs get complex or involve lots of filtering, I’d recommend checking out MeiliSearch or Algolia. They both support powerful searches and can be integrated easily with frontend libraries like instantsearch.js.
How should I format the keyword table? Would it just be a list like Keyword: word, with varying lengths?