How can I build one public search across multiple report databases?
Asked by
MellowCedar42
I work for a department with a public website that lets people search reports from different years. Because the reporting rules changed over time, the reports are split across several databases, and each group currently has its own search page. My supervisors want one search that can search all of the databases at once, both for internal staff and for visitors to the public website. The site runs on WordPress, and my programming experience is fairly basic. Is this practical to build? What information would I need to determine the right approach, and which skills should I learn first—SQL, PHP, WordPress development, APIs, or something else?
Answers
A common solution is to create a central search index or data warehouse. Data from each source is copied or synchronized into a consistent structure, and the website searches that central system instead of querying every database directly. There are commercial tools that can help, but someone still needs to understand the data, map equivalent fields, and maintain the imports.
Reply to this answer
Another approach is to put a small service in front of each database. Every service exposes the same kind of search interface, and a central application sends the query to each one, combines the results, and returns them to the website. That makes it easier to add another database later and avoids exposing the databases directly to the public. It’s a solid design, but it requires experience with APIs, authentication, data mapping, and deployment.
Reply to this answer
Yes, it’s doable, but the difficulty depends heavily on how the databases are hosted and exposed. You’d need to know whether they’re relational databases, whether you have read-only access, whether existing APIs are available, and how different the report fields and date formats are. Security and privacy requirements also matter because this would connect public search functionality to potentially sensitive systems.
Reply to this answer
Before writing code, document where each database lives, how the existing search pages work, what access methods are available, and which fields should be searched across all systems. A prototype against one database can show whether the search requirements are realistic. For a public production system, it would be wise to involve an experienced engineer because authentication, sensitive data, backups, monitoring, and accidental exposure are easy to get wrong.
Reply to this answer
SQL is useful, but it isn’t the whole project. Start with SELECT, WHERE, JOIN, UNION, indexes, views, and full-text search. Since WordPress uses PHP, basic PHP and custom plugin development would also help. A plugin could provide the search form and call a backend service or read-only database connection. Use parameterized queries and strict access controls—never build SQL by concatenating user input.
Reply to this answer
Add your answer
Answers are reviewed before publication.
Related Discussions