conditional inner join?

Im faced with the following design issue..

on my site there are different profiles: a city profile, a restaurant profile and a user profile.

in my DB:
City profiles are stored in tbCities
cityID int PK
shortname nvarchar(50)
forumID int FK
(...)

Restaurant profiles are stored in tbRests
restID int PK
shortname nvarchar(50)
forumID int FK
(...)

User profiles are stored in tbUsers
userID int PK
shortname nvarchar(50)
forumID int FK
(...)

as you can see a single ID value (for CityID,restID or userid) might occur in multiple tables (e.g. ID 12 may exist in tbRests and in tbUsers)
Each of these profile owners can start a forum on their profile.

forumID in each of the above tables is a FK to the PK in tbForums:
forumID int
forumname nvarchar(50)
(...)

Now imagine the following:

a site visitor searches ALL forums...say he finds the following forums:
ForumID Forumname
1 you opinion on politics
2 is there life in space?
3 who should be the next president of the USA?

a user may want to click on the forum name to go to the profile the forum belongs to.
And then there's a problem, because I dont know in which table I should look for the forum ID...
ORI would have to scan all tables (tbCities,tbRests and tbUsers) for that specific forumid,which is time-consuming andI dont want that!

so if a user would click on forumID 2 (is there life in space?)

I want to do a conditional inner join for the tablecontainingforumID (which may be tbCities,tbRests or tbUsers)

select tablecontainingforumID.shortname FROM tablecontainingforumID t
INNER JOIN tbForums f ON t.ForumID=f.ForumID
where f.ForumID=2

I hope my problem is clear..any suggestions are welcome (im even willing to change my DB design if that would increase effectivity)

Honestly here is your problem. You need to join up the table depending on which subTable contains the forum, the problem is you don't know the forum table ahead of time. So you are going to have to do this dynamically, or alternatively create a view which is a UNION of all those tables, and then join to the View. The question is which is going to perform better for you. The nice thing about Views is that like stored procs they are compiled. The bad thing is there are no indexes. So if you table is over a 10000 rows I would forget about the view. You can either use dynamic sql in a stored proc, or use a big case or if...else statement inorder to pick the correct join you want to do. Other than that I don't know what to tell you. The design of the database doesn't give you much flexability, especially since these table seem very close to each other as far as design. You may be able to combine them.|||One alternative is to divide your ID's across profiles as in 1-100000 IDs for cities, 200000+ for restaurants, 400000+ for users etc. So based on the ID you can directly join the particular table.|||

Or may be you could do it like this:

IF EXISTS( SELECT * From tblCities where forumid = @.forumid)

Begin

-- Join with tblCities

SELECT * from tblforums TF JOIN tblCities TC on ......

End

Else IF Exists (SELECT * FROM TblRests Where forumid = @.forumid)

Begin

-- Join with tblrests

End

and so on..

|||

hmmm...IMHO...your first solution is quicker...but design technical it seems that the second solution is better (also with scalability in mind)

but with say 5000000 user profiles and 500000 restaurant profiles and 1000000 city profiles...what would you advice?

|||

Is it possible to designate from the Forum table which table to look in?

What this means is that you add another column to the Forum table and set the values equal to city or restaurant or user. Then you can use a simple switch statement like the one above to switch between the different lookups you need to do.

On the flip side you could combine all three tables, give the unqiue column amount the three a common ID name, and set a field that tells what other table it should look at. That way you are only joining against one table.

Or you could just combine both ways above and merge everything in to the Forum table including the shortname and the unique id.

However if you don't want to change your current schema, I would index the ForumID's and just check if the ID exists in each table. However make sure they are checked in order or which one gets hit the most. So if you city table gets hit the most put it as the first check, so that you won't have to check through the other tables.

Basically there are many solutions, you just need to find that one that works best for you.

|||The first one would work but there are some caveats. You have to be able to see well into future and take a good guess about the number of records the table might have. So you dont cross over into the next bracket. Lets say you allocate 1-100000 to cities and 1000001-500000 to restaurants. There could be a situation where you have more cities and one day reach the 100000th mark and you will be left out of numbers. Of course you could still be a little create and assign another batch of numbers say from 800000-900000 but you would have to change your code again. I would prefer my second solution which does not involve changing data. Also, as nberardi mentioned, make sure you have proper indexes and you could line up the tables in the order in which they are likely to get hit first.