Design and query feedback please - thanks

[flyingfrog]

I have two tables:
Table Essays: (Primary Key) ID, Title, Author,Abstract, file location
Table Subjects: (Foriegn key) ID, section

An essay (in Table Essays) may be in more then one section. Hence no primary key in Table Subjects. Only a foreign key (tho I cant see a way of defining it as such).

Also I've written the following search (which works) but are there any obvious flaws with it.

SELECT * FROM essays
where ID IN
(SELECT ID FROM subject
WHERE section LIKE 'Biology'
)

This filters all the (essay) ID numbers from Table Subject where the field section = Biology, Then I filter all the records from the essays table whose ID
numbers matches the above filter.

Seems to work on a very sample I've tried. Any feedback.

Thanks

[oesxyl]

I have two tables:
Table Essays: (Primary Key) ID, Title, Author,Abstract, file location
Table Subjects: (Foriegn key) ID, section

An essay (in Table Essays) may be in more then one section. Hence no primary key in Table Subjects. Only a foreign key (tho I cant see a way of defining it as such).

Also I've written the following search (which works) but are there any obvious flaws with it.

SELECT * FROM essays
where ID IN
(SELECT ID FROM subject
WHERE section LIKE 'Biology'
)

This filters all the (essay) ID numbers from Table Subject where the field section = Biology, Then I filter all the records from the essays table whose ID
numbers matches the above filter.

Seems to work on a very sample I've tried. Any feedback.

Thanks
I guess subjects.id is same as essays.id so the query could be

select essays.*, subjects.section
from essays, subjects
where subjects.id = essays.id and subjects.section like 'Biology';


regards

[flyingfrog]

I guess subjects.id is same as essays.id so the query could be

select essays.*, subjects.section
from essays, subjects
where subjects.id = essays.id and subjects.section like 'Biology';


regards


Thanks for that example. I'm slowing starting to understand queries :) Is everything else about the design okay ?

[oesxyl]

Thanks for that example. I'm slowing starting to understand queries :) Is everything else about the design okay ?
as is now yes, is ok. If you have many records you probably want to add indexes to some fields. Probably you want later to extend and that could change the tables structure by adding new fields.

best regards

[flyingfrog]

as is now yes, is ok. If you have many records you probably want to add indexes to some fields. Probably you want later to extend and that could change the tables structure by adding new fields.

best regards

Excellent. thank you

[oesxyl]

Excellent. thank you

this probably could help:

http://www.utexas.edu/its/archive/windows/database/datamodeling/index.html

best regards