Some assistance with query needed -_-

I've got a website with dynamic content, each page (subject) got an ID. On every page there can be a number of links. These are either links to internal other pages on that website or external links.

For the internal links the only thing I need is the ID and Title of that page. Those can be found in the Tbl_subjects. As for external links I need ID, Title and URL which can be found in the Tbl_ext_links.

I've got a table named Tbl_linkboxes with:
- a Subject ID which means that this link belongs on this subject page.
- Link ID which is either an ID from Tbl_subjects or Tbl_ext_links
- External a boolean column to indicate if the Link ID refers to the Subject table or the External links table

There's basically 2 questions:
1) How to make this work? I've got a query below as feeble attempt
2) Should I really really really consider to use 2 columns for IDs and removing the External boolean. And simply setting one of those fields in the columns to >0 while the other is 0.

Okay, here's my attempt


Not sure if I should work with IIF here to make it work or something else. I'm almost tempted to kick the boolean column overboard and introduce a JOINT on both columns then, one for external link ids and other for internal page ids.

Amazing how long one can stare at a query and not being able to get it right

this is access, not sql server, right? if so, we should move this thread out of the sql server forum

can you please give a few rows of each table so that we can see the column names and how they relate

No, not necessarily Access. It was just a feeble attempt at a solution, but I failed miserably Most likely the website will be run on MS SQL (or possibly Cybase; very small possibility). A more general SQL solution would do the trick for more databases. But I'm a bit poor on knowledge to solve it. Especially when it comes to in-depth knowlegde of MS SQL (and Cybase). Are their some database specific methods which can help out here?

tbl_subjects:
sub_id
title
dep_id
contents

tbl_ext_links:
ext_id
title
url

tbl_linkboxes:
(which (ext) pages should be linked on specific page)
id
sub_id
link_id (ext_id / sub_id: either from ext_link or subjects table)
external (boolean; am i an external link or subjects page)

The Link ID is either a ext_id or a sub_id and external defines which table we're talking about.

oh, i see

well, since these tables don't exist yet, you have the perfect opportunity to design them for simplicity

what you should do is combine the subjects and external links table into one table

then your linkboxes table would not need two different link ids, nor the boolean switch either

The subjects and the external URLs are two different things. Basically the subjects or their IDs are the contents which is loaded into a template.

In a linkbox at the top of such retrieved page (with the contents of a specified subject_ID) there are a number of links which are of relevance to this particular page. This can either be another page in the same website and hence the ID (for the contents and all the rest that goes with the template) or an actual URL with nothing further.

The subjects table is simplified in the situation above. In reality there's a lot more fields involved, but not necessary for this explanation. I think it's messy to mix an URL to refer to a page outside the website, with the actual paragraphed contents of all pages (by ID) of this website. That's why I wish to keep them apart. Mixing both seems to complicate things more in the end.

okay, that's fine then, keep them separate

just use two separate related link fields in the linkboxes table, and ensure one or the other will always be null, then you don't need the boolean field

So it's not possible to have some sort of IF statement in the SQL query with DB like Sybase/MS SQL? That would have been my first choice and hence my apparently "access"-like attempt, since a boolean column takes up less space compared to an integer column per record.

i wouldn't use an "if" structure, i would join to both tables

the rows which will match ar the ones which don't have nulls

Okay, i think i'll follow up on your advice as the search for the if method hasn't been quite rewarding so far.

Thanks

I'm already digging into recursive stored procedures for reasons of building up some hierarchical menu. Well it returns in more things such as posts and replies to posts and replies to those replies. Really need to figure out how to get those procedures to work But will do some work on my own before resorting to forum. To see if I can tackle it myself Too bad I lack proper documentation.

It's simply uncool to realize something was overlooked when setting up a database Only got myself to blame for it.

As discussed earlier there were 2 kinds of items. Subjects table with a column for the actual contents and some table for plain URLs pointing to remote places, outside the scope of the website.

It was proposed that maybe I oughta combine both tables into one. I was not so happy about that as those external urls basically consist of nothing else but an URL, a title and an ID so they can be linked. Meanwhile the subjects table got plenty of columns with info required. Title, actual contents, ID and category ID are less than half of the fields. With the category ID I could link them to the categories and basically build the menu on the fly. Neat. The problem I face now is...

I also got subjects which are local Word Docs and PDF files. Those appear in the menu, like the rest of the subjects, but they don't have plain text contents which needs to be put inside a template on request by ID.

So what to do? Putting those URLs in the subjects table? Though last-modified and some other fields don't apply to these PDF in the DB, the order_number and such in which they should appear in the menu or how they link to the category is appliable to these. So how to integrate it in the DB?

A few choices I thought of:
1). Simply add a column named "url" or so and keep either "url" or "contents" NULL so it can be decided whether it concerns some file to load and open, or contents which needs to be retrieved and merged with a template for output to the screen.
2). Put the url link as well as the contents in one field, introduce a new column which defines a "type". To say that the other field is either a PDF url or plain text to be used for a template.
3). The first option, but also introducing a column to give some information about the type: it's a PDF, it's a DOC, it's a whatever or it's plain text so grab that plain text from "contents" and the rest from "url".

4). Or... and this is maybe a bit more drastic so let me define the entire Subjects table here:
Sub_id (PK)
Title
Order_no
Category_id (FK to category table)
Description
Contents
Created_date
Last_modified (now())

4th option would be: separate things and keep together what can be kept. Example:

Subjects Table:
Sub_id (PK)
Title
Order_no
Category_id (FK)
Content_id (FK)
File_id (FK)

Contents Table:
Content_id (PK)
Description
Contents
Last_modified (now())

File Table:
File_id (PK)
URL

Like with those Linkboxes it would mean 2 columns for 2 types and keeping one NULL while the other is set.

Why all this trouble? Well, for a menu I have to be able to access ALL subjects (except for external links coz they are shown separately). All subjects got an order number and a title and they have to be shown accordingly in the menu.

That's why they have to be in the Subjects table, whether plain text or a file to be linked to on the local site. But I could split them from there.

My question is.... 1) does it make sense what I wrote and 2) would it be dirty to put everything inside one table which would give me unnecessary empty fields. Or is it really best to split it into the two tables and link it to the subjects Table. Following the books about databases I would say that option 4 is best, but I'd like some feedback from others if it sounds good.

as someone who has seen and worked with all those methods, i can tell you that they all work and they all have advantages and disadvantages

to find more info on this subject, search for superset/subset in the context of data modelling

my own preference is a single table with null fields as required

why? because taken overall, the sql is simpler

what you should do it consider each option's impact on your sql

given the point you are at in designing this application, you should have a pretty good idea what sort of application functionality you need -- insert new entries, change entries, produce lists, produce reports, do searches, etc. -- so you should already have an idea what the sql will be that you need

write some of this sql for each option and take a look at how easy/hard it is

you will find that some options have more complex sql

"unnecessary empty fields" is not, in my opinion, a bad thing compared to complex sql

What's important in the end is speed.

Personally I'd say:

The situation with 2 tables and 2 id-columns in the parent:
Parent.Column1 (FK) --> Sub1.ID (PK)
Parent.Column2 (FK) --> Sub2.ID (PK)
Means I have to perform two LEFT JOINS when doing a SELECT and use ASP to see which one got something useful. How much additional time do two LEFT JOINS take?

The situation with an URL and Content in the parent:
Means there will be a number of NULL fields. Is it a problem? Not unless we have to perform queries with NULL in the WHERE condition. I read it can slow down performance.

The situation with an URL and Content which both reside inside a Content column:
There's some dislike from me personally as one column would be used for 2 purposes... URLs to be loaded as location and Content which is parsed with a template and shown. Question is if it's widely accepted or actually indeed dirty. Plus we'd have to introduce some column to define the type of content, because if someone comes up with the idea of having an URL as text (silly as it may be) it would be interpreted as URL and not parsed with the template.

But in the end it's about performance. And therefore I could really use your opinion since you have much experience it seems. It's for an intranet so basically it means there will be updates, but it's mainly a source for requiring information and thus selects selects and more selects.

From what I read you already gave your opinion by putting it in one table. I suppose you agree with me that it's best to introduce an URL column?