Database design query - how do I join these tables?

Question

Hi, I've got the following tables in a CMS database (SQL Server 2008) for a website:

SiteMap       Page          Link
=========     =========     =========
SiteMapId     PageId        LinkId
LeftNode      Name          Name
RightNode     UrlId         Url
              -- etc --     -- etc --

• Sitemap table contains the website hierarchy using the nested set model, used primarily for generating the website navigation.
• Page table contains data for pages served by the CMS system.
• Link table contains URLs that are displayed on the site navigation, but not served by the CMS (e.g. external links to other sites).

Each SiteMap row needs to link to one row in either the Page or the Link table, and there lies the problem. What's the best way?

1. Add a SiteMapId foreign key to both the Page and the Link tables - downside: allows the same SiteMap row to be referenced by more than one record (in both tables).
2. Add PageId and LinkId columns to the SiteMap table - downside: one column will be redundant as it will always contain null or 0.
3. Add a PageOrLinkId column to the SiteMap table and a bit field column to mark it as containing a PageId or LinkId - downside: can't add constraints to the relationships to ensure they contain valid values.

Can anyone suggest which of the above I should use, or come up with a better option? Thanks.

Answer 1

You could have a many-to-many relationship supported by these tables as another option:

SiteMapPage
SiteMapId
PageId

SiteMapLink
SiteMapId
LinkId

edit: but upon reflection is unlikely to be of any more use than your option 1...sorry!

Answer 2

If each SiteMap can only be connected with one page or link, then I would choose the first option. In this case you would not have null values as in the second option and no unecessary columns as with the third option.

Getting all the pages (or links) would be quite easy as it's just a simple inner join.

Answer 3

Another posibility: Add a new table with SiteMapId, PageId and LinkId and add constraints (((PageId IS NULL OR LinkId IS NULL) AND (PageId IS NOT NULL OR LinkId IS NOT NULL)) AND SiteMapId IS NOT NULL))

You would then be able to join using the new table to the other two tables...just my 30 second off the cuff thought. (*untested)

Answer 4

What about having a table called SiteItem with the SiteMapID in it, and a 1-1 relation with both the Page and Link tables, that is an entry in the SiteItem table has an entry in either the page or the link table. In this case, the Page and Link tables are like sub classes of the SiteItem table.

Answer 5

Personally I thing option 2 is the simplest to do to ensure your one and only one record is enforced. A trigger should be put into place to ensure that at least one of the two values is null. There is nothing inherently bad about having null values in a case like this.

Answer 6

Solved this in the end by adding constraints directly to the primary keys so SiteMapId is an autonumber/identity with a one-to-one relationship with PageId and LinkId. It doesn't stop the addition of both a Page and a Link row for one SiteMap row, but avoids any redundant columns or additional tables.