[Tutor] DB design

[Alan Gauld]

> I'm already hitting my conceptual troubles however, as I'm
visualising
> each table as a 'card'.

Maybe but the cards are made up of rows, each row with fields.
Think like a spreadsheet. Each sheet can have references to
other sheets - like Tabs in Excel....

> dimensional. But what I was wondering was is it possible within the
> bounds of SQL to contain a SQL query reference as a value, so that
> retrieving a certain value retrieves the results of that query i.e.

Not quite sure what you mean, but lets try an example.

create table foo (id number, food varchar);
create table b (id, fooID number, val varchar);
insert into foo values(1,'Spam');
insert into foo values(2,'Chips');
insert into foo values(3,'Bread');
insert into bar values(1,2,'Sauce');

Now notice that bar has a reference to foo.
And the last insert creates a row with a reference to foo id 2

So now we can do a select that finds data based on
the reference:

SELECT food, value from foo,bar
WHERE bar.fooID = foo.id

Returns:

Chips, Sauce


Alternatively we can nest select statements:

SELECT value from bar
WHERE fooID in (SELECT id from foo);

The result (in this case!) should be the same but the
efficiency is a lot lower!

> I'm just trying to make my cards more 3D in terms of linking
data....

Dunno if that clarified anything?

Alan G
Author of the Learn to Program web tutor
http://www.freenetpages.co.uk/hp/alan.gauld