Combine multiple tables with exactly the same data types and add a column to identify the table of origin

Question

We're in the process of building a website, that hosts a total of 8 static webpages. Each of the webpages is generated, using a 'Content' table/model.

So we have a HomeContent and a FAQContent table etc.

Each of these tables/models contains the following 3 columns:

Columns:

Id | Type | Content

Example data:

1 | h1   | "Welcome to our website!"

Now, I'm a noob and I'm having a hard time with creating a search functionality for our website. This search functionality, must allow users to get a search result page, containing links to webpages where their search word is located.

My idea is to combine all the Content tables we have, into one big Content table, and to add a column to this big Content table called 'Location', which will contain the type of table, from which the data originally came.

So I would hopefully get something like:

Id | Type | Content      | Location 
---+------+--------------+------------
1  | h1   | "Welcome!"   | Home
2  | label| "Weather"    | Info
3  | h4   | "Hello there"| Contact

There is probably a much easier way to create a working website search engine, but i am (frustratingly) not knowledgeable enough to think of any other way...

Can anyone please help me out?

Edit:

Additional info: and an example of the HomeContent model that is used for our homepage:

public class HomeContent
{
    public int Id { get; set; }
    public string Type { get; set; }
    public string Content { get; set; }
}

Answer 1

You should fix your data model and store all contents in a unique table, with a column that contains the location each row belongs to.

In the meantime, you can use union all to create a view that to emulates that:

create view AllContents as
select id, type, content, 'Home' location from HomeContent
union all select id, type, content, 'Info' from InfoContent
union all ...

Then you can search the view:

select * from AllContents where content like '%mySearchValue%'

As commented by Gordon Linoff, you might also want to have a look at the full text search functionality, that provides a built-in solution to perform efficient text searches.