Searching Across Multiple Tables

Question

My application has some Pages which possess some Attributes. Pages can be Linked together. I would like my database structure to best support search for Links which contain certain Attributes that belong to Pages. Links are a collection of Pages and Page can have many Links.

---

My database stucture is currently as follows

PAGE:
    id
    title
    content

-

ATTRIBUTE:
    id
    page_id
    name
    value

-

LINK:
    id
    title

-

PAGE_LINK:
    page_id
    link_id

---

So say I had a recipe Page called 'Mash Potato' and an ingredients Page called 'Potato' that are contained in a Link called 'Potato Link'. 'Mash Potato' has the following Attributes - Meal:Dinner, Flavour: Savoury, Difficulty: Easy, AND 'Potato' has the Attributes - Season: Autumn, Price: Cheap.

I want to be able to find a link which is Cheap, Savoury and in Season in Autumn and get in return the 'Potato Link'.

---

How do I write that query in SQL and is this the best database structure to query something like this?

Answer 1

Your attributes are attached to pages. So, you can search for pages that have certain attributes, by checking if those Attributes exist for a page. Finding the pages would look like this:

Select Page.ID
From Page
where EXISTS
 (Select * 
  From Attributes
  Where Page_Id = Page.ID
    and (     (Name = 'Season' and Value = 'Autumn')
          or  (Name = 'Flavour' and Value = 'Savory')
          ... etc. ...
        )

If you want to find the Links, then you can join this to PAGE_LINK (and even to LINK, if you like).

Select Page.ID
From Page
 Join Page_Link PL on PL.Page_ID = Page.ID
 Join Link on Link.ID = PL.Link_ID
where EXISTS
 (Select * 
  From Attributes
  Where Page_Id = Page.ID
    and (     (Name = 'Season' and Value = 'Autumn')
          or  (Name = 'Flavour' and Value = 'Savory')
          ... etc. ...
        )