Joining tables with anonymous foreign keys

Related

Related to my other question:

Comment system design

开发者_开发百科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?

This would be "the usual" approach.

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.

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.

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