How can I build one public search across multiple report databases?
Asked by
MellowCedar42
I work for a department whose public website lets people search reports from different years. Because the reporting rules changed over time, the records are stored in separate databases, and there are currently different search tools for the old and new rule sets. My supervisors would like one search that can query all of the sources at once, both for internal use and for visitors to the public website. The site runs on WordPress, and my programming experience is fairly basic. Is this practical, and what technologies or skills should I learn? Would SQL be the main starting point?
Answers
Yes, it’s possible, but the difficulty depends on how many systems are involved and how they expose their data. First find out where each database is hosted, whether the current search tools are custom pages or plugins, whether you can get read-only database access or use APIs, and how different the report fields and date formats are. Since the site is public, access control and protecting sensitive information are just as important as the search itself.
Reply to this answer
SQL is definitely useful if these are relational databases. You’d want to learn SELECT, WHERE, JOIN, UNION, views, indexes, and full-text search. For WordPress, the front end could be a custom PHP plugin with a search form and a backend that queries each source, but use prepared statements and read-only accounts to avoid SQL injection and limit damage if something goes wrong. Basic REST APIs and JavaScript may also help if the existing systems only provide web services.
Reply to this answer
A common architecture is to put a standard service in front of each database. Each service translates the local schema into the same format, and a separate search service combines the results. That makes it easier to add another database later and keeps the public website from connecting directly to every database. You could also copy and normalize the records into a data warehouse or search index, which is often faster and safer for public searches, but it requires a process to keep the data synchronized.
Reply to this answer
There are commercial tools and reporting platforms that can combine data sources, but they still require someone to understand the records, permissions, and search requirements. This isn’t necessarily impossible, but it’s more than just adding a WordPress search box. Before choosing a solution, document the databases, fields, authentication rules, expected search volume, and what information is allowed to be publicly searchable. A senior developer or database engineer would be valuable for reviewing the design and security.
Reply to this answer
Add your answer
Answers are reviewed before publication.
Related Discussions