Joining tables with anonymous foreign keys

Question

Related

Related to my other question:

Data Design

Let's say I have a tags table:

tblTags
-------------
TagID (int)
Name (string)

And two content tables:

tblBlogs
-------------
Anchor (GUID, Primary Key)
BlogTitle (string)
+ More custom fields

tblTutorials
-------------
Anchor (GUID, Primary Key)
TutorialTitle (string)
+ More custom fields

There will be more tables with anchors as well, it's not just 2.

Then to associate a tag with an above entity:

tblTagAnchors
-------------
TagID (int, Foreign Key)
Anchor (GUID, Foreign Key)

My question is, once I have built up my associations of blogs and tutorials with a specific tag, is there any way to write a query to return blogs or tutorials with a specific tag? Without needing to have separate queries for both the Blogs and Tutorials?

The main use would be for search, something along the lines of (pseudo):

select from tblBlogs and tblTutorials where the GUID exists in tblTagAnchors where tagID = 5

for each record returned
    if record from Blog
        response.write("<a href=blogView.aspx?ID=" + recID)
    else if record from Tutorial
        response.write("<a href=tutorialView.aspx?ID=" + recID)
next

I'm using SQL Server 2008 Express, and ASP.net 4 (c#) if it makes much difference with Linq to SQL, but a design based answer is all I need, not any code necesserially unless for demonstration.

Is the only way to do this with multiple queries?

Damir Sudarevic · Answer 1 · 2011-04-18T14:27:22.477

5

This would be "the usual" approach.

enter image description here

select
      p.PublicationID 
    , p.PublicationType 
    , p.PublicationTitle
    , t.TagID
    -- other blog/tutorial specific fields here
from Publication    as p
left join Blog      as b on (b.PublicationID = p.PublicationID and p.PublicationType = 'B')
left join Tutorial  as t on (t.PublicationID = p.PublicationID and p.PublicationType = 'T')
join PublicationTag as x on x.PublicationID = p.PublicationID
join Tag            as t on t.TagID = x.TagID ;

You may package this into a view to help isolate any future schema changes from the application code.

edited Apr 18 '11 at 14:27

answered Apr 18 '11 at 13:53

Damir Sudarevic

21,891
3
47
71

Thanks for this and an alternative design, really appreciate your time – Tom Gullen Apr 19 '11 at 08:32

score 3 · Accepted Answer · edited Apr 18 '11 at 12:12

3

I would recommend the use of a union. This query would only have one result set but under the hood it is merging to queries together.

Select b.rec_id,'Blog' as type from tblBlogs b
inner join tblTagAnchors ta on ta.anchor = b.anchor
where ta.tagid = 5
union
Select t.rec_id,'Tutorials' as type from tblTutorials t
inner join tblTagAnchors ta on ta.anchor = t.anchor
where ta.tagid = 5

Then in your vb code just do an if on the type field. In linq you will have to write the query using .union command. I just wrote a generic sql solution. It can easily be transformed to linq though.

As a side note conditional foreign keys makes me want to shutter. In modern database design you should always avoid a key that can go to multiple tables it is hard to enforce CRUD and more confusing to query. I would suggest creating a super type of tblBlogs and tblTutorials like tblWebsites and making the key go to the supertype.

edited Apr 18 '11 at 12:12

Andriy M

76,112
17
94
154

answered Apr 18 '11 at 11:51

JStead

1,710
11
12

1

I agree with @JStead on his answer. You have two tables, so you need two queries (in your example) and Union will merge them together into one result set. But, his comment about "conditional foreign keys makes me want to shutter" is a little confusing to me as your design is the classic way of handling a many-to-many table design. One tag can be in many blogs (or tutorials) and one blog (or tutorial) can have many tags. Thus, you need a many-to-many table (tblTagAnchors) to keep track of them--I'm not sure what other choice you would have. Nor does it violate database design normalization. – Jim Apr 18 '11 at 12:18
I will outline my design when I get to work but my problems wasn't the fact of having the association table. You will have an association table here no matter what. The main problem that my query illustrates is you have a foreign key relationship that is dependent on the type of the website. – JStead Apr 18 '11 at 12:29
The union-based query will be more efficient than the left-outer-join-based query. – Philip Kelley Apr 18 '11 at 14:15
@Jim check out Damir's implementation. Work got a little busy at the start but that is exactly how I would have implemented this design. – JStead Apr 18 '11 at 17:33
Thanks for this answer, I marked it as correct because it answers the original question. – Tom Gullen Apr 19 '11 at 08:31

Andriy M · Answer 3 · 2011-04-18T13:14:52.223

Not only can you select the necessary data using one query, but you can also get rid of the if condition in the client, the one which decides which href to output, because you can select data and construct the output strings at the same time, in the same query. Your client would then only have to iterate through the result set and output the strings.

Basically, you query would be a UNION of two subqueries, but I would do that somewhat differently from how @JStead has offered, something like this possibly:

SELECT
  OutputString = '<a href=' + SrcName + 'View.aspx?ID=' + CAST(x.recID AS varchar)
FROM (
  SELECT 'blog' AS SrcName, Anchor, recID
  FROM tblBlogs
  UNION ALL
  SELECT 'tutorial' AS SrcName, Anchor, recID
  FROM tblTutorials
) x
  INNER JOIN tblTagAnchors ta ON x.Anchor = ta.Anchor

As you can see, the query returns data ready to be output. Therefore your client's logic gets simplified to something like this:

for each record returned
    response.write(OutputString)
next

Joining tables with anonymous foreign keys

3 Answers3