DB structure question

[Alistair]

Access 2000.

I'm trying to create a DB that stores records of books, but my understanding
of DBases is very simple at best

so, I have a table that for simplicities sake has

Bob book1 comments
Joe book 2 comments
Fred book 3 comments

That works fine, until...

bob also reads book 4

so now I would have

Bob book1 comments
Joe book 2 comments
Fred book 3 comments
Bob book 4 comments

now, it is my understanding that as Bob is now duplicated I should create a
new table for bob and link this to the other table (relational DB??) so that
there is only one "Bob" in the table?

but, what happens if Joe also reads book 4?

now, book 4 is duplicated and so should this be placed in another table??

if so, how do I link them all together so that if I search for Bob, I get
all the books bob has read and all the comments he has written

or if I serach for book 4, I get who has read it and the comments?

I hope I'm making this clear...and it makes sense to someone...

many thanks for any advice

Alistair

[Raymond D'Anjou \(raydan\)]

Create a book table and a reader table.
Then you can create a bookRead table to link the two.

Quick example:
books (bookID, author, name, ...)
readers(readerID, lastName, firstName, ...)
bookRead (bookID, readerID, dateRead, comments)

"Alistair" <forget_it> wrote in message
news:109cd70hl4s7r21@corp.supernews.com...

> Access 2000.
>
> I'm trying to create a DB that stores records of books, but my

understanding

> of DBases is very simple at best
>
> so, I have a table that for simplicities sake has
>
> Bob book1 comments
> Joe book 2 comments
> Fred book 3 comments
>
> That works fine, until...
>
> bob also reads book 4
>
> so now I would have
>
> Bob book1 comments
> Joe book 2 comments
> Fred book 3 comments
> Bob book 4 comments
>
> now, it is my understanding that as Bob is now duplicated I should create

a

> new table for bob and link this to the other table (relational DB??) so

that

> there is only one "Bob" in the table?
>
> but, what happens if Joe also reads book 4?
>
> now, book 4 is duplicated and so should this be placed in another table??
>
> if so, how do I link them all together so that if I search for Bob, I get
> all the books bob has read and all the comments he has written
>
> or if I serach for book 4, I get who has read it and the comments?
>
> I hope I'm making this clear...and it makes sense to someone...
>
> many thanks for any advice
>
> Alistair
>
>
>
>
>
>

[Alistair]

"Raymond D'Anjou (raydan)" <raydan@canatrade.nospamcom> wrote in message
news:uYe8RuQMEHA.2532@TK2MSFTNGP10.phx.gbl...

> Create a book table and a reader table.
> Then you can create a bookRead table to link the two.
>
> Quick example:
> books (bookID, author, name, ...)
> readers(readerID, lastName, firstName, ...)
> bookRead (bookID, readerID, dateRead, comments)
>

Thanks, that seems to make sense to me.

so in table 1, I have

Name, Name_Unique_ID, any_other_details etc

table 2

Book_name, Book_Unique_ID, etc

and table 3

Unique_ID, name_unique_ID, Book_unique_ID, comments, date etc etc

so that if Bob, for example (unique_ID_bob), reads book1 (unique_ID_book1)
then a record is created in table 3 that includes a unique indentifier
itself, plus the unique identifiers for both bob and book1?

thing is, I've never built a relational database before so I'm not sure how
to go about it. I can create the tables and I'm pretty sure I'll have no
trouble actually inserting the data and retrieving it, but it's the linking
of the tables that I might struggle with.

any sites that can talk me through it?

many thanks indeed

[Raymond D'Anjou \(raydan\)]

Inline:

> Thanks, that seems to make sense to me.
> so in table 1, I have
> Name, Name_Unique_ID, any_other_details etc

Don't forget to set the primary key.

> table 2
> Book_name, Book_Unique_ID, etc

Don't forget to set the primary key.

> and table 3
> Unique_ID, name_unique_ID, Book_unique_ID, comments, date etc etc

You don't really need the unique_ID column.
name_unique_ID & Book_unique_ID would be the primary key.
The combination of both is unique.

> so that if Bob, for example (unique_ID_bob), reads book1 (unique_ID_book1)
> then a record is created in table 3 that includes a unique indentifier
> itself, plus the unique identifiers for both bob and book1?

> thing is, I've never built a relational database before so I'm not sure

how

> to go about it. I can create the tables and I'm pretty sure I'll have no
> trouble actually inserting the data and retrieving it, but it's the

linking

> of the tables that I might struggle with.

I haven't used Access in a long while but if I remember correctly you have a
graphical tool, "Relationships" I think, that you can use to link the
tables.

> any sites that can talk me through it?

First of all, search Google for sites on database modeling in general.
You could get help for specifics in Access help.

> many thanks indeed
>
>

[Aaron Bertrand - MVP]

> name_unique_ID & Book_unique_ID would be the primary key.

> The combination of both is unique.

Well, that depends. Can Bob submit multiple comments for Catcher in the
Rye? I know on Amazon I can review the same title multiple times.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Alistair]

"Raymond D'Anjou (raydan)" <raydan@canatrade.nospamcom> wrote in message
news:eeadblRMEHA.624@TK2MSFTNGP11.phx.gbl...

> Inline:
>

> > Thanks, that seems to make sense to me.
> > so in table 1, I have
> > Name, Name_Unique_ID, any_other_details etc

>
> Don't forget to set the primary key.
>

> > table 2
> > Book_name, Book_Unique_ID, etc

>
> Don't forget to set the primary key.
>

> > and table 3
> > Unique_ID, name_unique_ID, Book_unique_ID, comments, date etc etc

>
> You don't really need the unique_ID column.
> name_unique_ID & Book_unique_ID would be the primary key.
> The combination of both is unique.
>

> > so that if Bob, for example (unique_ID_bob), reads book1

(unique_ID_book1)

> > then a record is created in table 3 that includes a unique indentifier
> > itself, plus the unique identifiers for both bob and book1?

>

> > thing is, I've never built a relational database before so I'm not sure

> how

> > to go about it. I can create the tables and I'm pretty sure I'll have no
> > trouble actually inserting the data and retrieving it, but it's the

> linking

> > of the tables that I might struggle with.

>
> I haven't used Access in a long while but if I remember correctly you have

a

> graphical tool, "Relationships" I think, that you can use to link the
> tables.
>

> > any sites that can talk me through it?

>
> First of all, search Google for sites on database modeling in general.
> You could get help for specifics in Access help.
>

> > many thanks indeed
> >
> >

>

ok

USERS TABLE

record_ID (auto number)
name
email (primary key)
password


BOOKS TABLE

record_ID (auto number)
book_title (primary key)


REVIEWS TABLE

record_ID (auto number)
unique_ID_book (linked to primary key in books table)
unique_ID_user (linked to primary key in users table)
review
date_reviewed

I linked the tables using the graphical interface in Access

now, what I'm not sure about is how I go about entering and retrieving the
data into these tables.

I can enter each users details into a new record (using simple form data)
and each is assigned a unique identifier (email address in this case), the
same thing with the books table (with book name being the unique
identifier).

then I insert into the reviews table, the unique identifier for the user,
the unique identifier for the book and the review data.

if it's not a new user or a new book then I simple add the unique identifier
for the user and the book into the review table along with the review

is this correct?

now...the data retrieval

do I perform a query on the reviews table, extract the data, and then use
this data to perform further queries on the users table and books table??

e.g lets say I'm looking for all reviews for book1,

I search the books table for the unique identifier for book1

Once I have the unique identifier for book1, I then search the reviews table
for all occurences of this identifer.

for each result I get, I then have to take the unique identfier for the
users who have read this book and the search the users table to get the
users details..


is that how it's done? and if so, I fail to see the need for the linking??

or am I missing something very obvious?

[Raymond D'Anjou \(raydan\)]

I had thought of that (I really did, beleive me). ;-)

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:O5eOG%23RMEHA.808@tk2msftngp13.phx.gbl...

> > name_unique_ID & Book_unique_ID would be the primary key.
> > The combination of both is unique.

>
> Well, that depends. Can Bob submit multiple comments for Catcher in the
> Rye? I know on Amazon I can review the same title multiple times.
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>

[Raymond D'Anjou \(raydan\)]

Inline:

> USERS TABLE
> record_ID (auto number)
> name
> email (primary key)
> password
>
> BOOKS TABLE
> record_ID (auto number)
> book_title (primary key)
>
> REVIEWS TABLE
> record_ID (auto number)
> unique_ID_book (linked to primary key in books table)
> unique_ID_user (linked to primary key in users table)
> review
> date_reviewed

> I linked the tables using the graphical interface in Access
>
> now, what I'm not sure about is how I go about entering and retrieving

the

> data into these tables.
>
> I can enter each users details into a new record (using simple form data)
> and each is assigned a unique identifier (email address in this case), the
> same thing with the books table (with book name being the unique
> identifier).
>
> then I insert into the reviews table, the unique identifier for the user,
> the unique identifier for the book and the review data.
>
> if it's not a new user or a new book then I simple add the unique

identifier

> for the user and the book into the review table along with the review
>
> is this correct?

Sounds OK.

> now...the data retrieval

> do I perform a query on the reviews table, extract the data, and then use
> this data to perform further queries on the users table and books table??

No.

> e.g lets say I'm looking for all reviews for book1,

> I search the books table for the unique identifier for book1

> Once I have the unique identifier for book1, I then search the reviews

table

> for all occurences of this identifer.

> for each result I get, I then have to take the unique identfier for the
> users who have read this book and the search the users table to get the
> users details..

> is that how it's done? and if so, I fail to see the need for the

linking??

No.

> or am I missing something very obvious?

Maybe not obvious. Read up on joins. Access must have something in its help
file.

[Aaron Bertrand - MVP]

> for each result I get, I then have to take the unique identfier for the

> users who have read this book and the search the users table to get the
> users details..

No, not at all. You perform what is called a JOIN. Some samples here,
[url]http://www.aspfaq.com/2241[/url] and also read up on general efficiency approaches
in [url]http://www.aspfaq.com/2424[/url]. And as Raydan stated, there are JOIN
examples in the Access help file. I don't think you should ever need or
want to nest recordsets in ASP. You've already sacrificed enough by
choosing Access in the first place. ;-)

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Alistair]

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:eSrPiTTMEHA.3972@TK2MSFTNGP10.phx.gbl...

> > for each result I get, I then have to take the unique identfier for the
> > users who have read this book and the search the users table to get the
> > users details..

>
> No, not at all. You perform what is called a JOIN. Some samples here,
> [url]http://www.aspfaq.com/2241[/url] and also read up on general efficiency

approaches

> in [url]http://www.aspfaq.com/2424[/url]. And as Raydan stated, there are JOIN
> examples in the Access help file. I don't think you should ever need or
> want to nest recordsets in ASP. You've already sacrificed enough by
> choosing Access in the first place. ;-)
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>

If I knew another databse I would use it, but my skills are limited in this
area.

and alas the help files in Access2000 are woefully poor...not even a sample
database to look at.

but I'm reading up on JOINS as we speak

[Raymond D'Anjou \(raydan\)]

Sorry, I was a bit swamped when I last answered and gave you that a bit
quick.
Good thing Aaron jumped in to give you a more explanations and links
....even though he chastized you on using Access.
We all have to start somewhere (I also had to start on Access).
Even Aaron may have already worked in Access (although he may not admit it).

"Alistair" <forget_it> wrote in message
news:109d1rffseefje7@corp.supernews.com...

>
> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
> news:eSrPiTTMEHA.3972@TK2MSFTNGP10.phx.gbl...

> > > for each result I get, I then have to take the unique identfier for

the

> > > users who have read this book and the search the users table to get

the

> > > users details..

> >
> > No, not at all. You perform what is called a JOIN. Some samples here,
> > [url]http://www.aspfaq.com/2241[/url] and also read up on general efficiency

> approaches

> > in [url]http://www.aspfaq.com/2424[/url]. And as Raydan stated, there are JOIN
> > examples in the Access help file. I don't think you should ever need or
> > want to nest recordsets in ASP. You've already sacrificed enough by
> > choosing Access in the first place. ;-)
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > [url]http://www.aspfaq.com/[/url]
> >
> >

> If I knew another databse I would use it, but my skills are limited in

this

> area.
>
> and alas the help files in Access2000 are woefully poor...not even a

sample

> database to look at.
>
> but I'm reading up on JOINS as we speak
>
>
>

[Alistair]

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:eSrPiTTMEHA.3972@TK2MSFTNGP10.phx.gbl...

> > for each result I get, I then have to take the unique identfier for the
> > users who have read this book and the search the users table to get the
> > users details..

>
> No, not at all. You perform what is called a JOIN. Some samples here,
> [url]http://www.aspfaq.com/2241[/url] and also read up on general efficiency

approaches

> in [url]http://www.aspfaq.com/2424[/url]. And as Raydan stated, there are JOIN
> examples in the Access help file. I don't think you should ever need or
> want to nest recordsets in ASP. You've already sacrificed enough by
> choosing Access in the first place. ;-)
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>

well, almost there I think....

after some help from fellow progammer I have

BOOK_USERS
userID (autonumber field linked to REVIEWS)
user_name
user_email

BOOKS
bookID (autonumber field linked to REVIEWS)
book_title

REVIEWS
userID
bookID
user_review


strSQL = "SELECT A.user_review, B.user_name, B.user_email, C.book_title " &
_
"FROM Reviews As A " & _
"INNER JOIN Book_users As B On (A.UserID = B.UserID) " & _
"INNER JOIN Books As C On (A.BookID = C.BookID) " & _
"WHERE C.BookID = 1 "

rs.Open strSQL, Conn, 1

however, this gives me a

Syntax error (missing operator) in query expression

I can't see the cause for this, as it indicates that I've used a reserved
word somewhere???

[Bob Barrows]

Alistair wrote:

>
> after some help from fellow progammer I have
>
> BOOK_USERS
> userID (autonumber field linked to REVIEWS)
> user_name
> user_email
>
> BOOKS
> bookID (autonumber field linked to REVIEWS)
> book_title
>
> REVIEWS
> userID
> bookID
> user_review
>
>
> strSQL = "SELECT A.user_review, B.user_name, B.user_email,
> C.book_title " & _
> "FROM Reviews As A " & _
> "INNER JOIN Book_users As B On (A.UserID = B.UserID) " & _
> "INNER JOIN Books As C On (A.BookID = C.BookID) " & _
> "WHERE C.BookID = 1 "
>
> rs.Open strSQL, Conn, 1
>
> however, this gives me a
>
> Syntax error (missing operator) in query expression
>
> I can't see the cause for this, as it indicates that I've used a
> reserved word somewhere???

In this case, no.
Remember, the first step to take in debugging a dynamic sql statement is to
write it to the response:

response.write strSQL

Running the page and looking at the statement written to the browser window
will usually show you the cause of the problem. We need to see the
response.written statement if it does not help you solve your problem.

Unfortunately, in this case, the problem is not so obvious. Your statement,
as written, would run without flaw in SQL Server. In Jet (Access), however,
it's a different story. Jet is very particular about the use of parentheses
when joining more than 2 tables. Frankly, I find it hard myself to remember
where to put the parentheses. So what do I do? I open my database in Access
and use the Access Query Builder to create my query using the GUI Design
View. Create a new query in Design View, add your 3 tables to the query from
the Choose Tables dialog, use the Properties window to assign aliases to
each table, click and drag the fields between the field lists to create your
joins, then switch to SQL View to see where Access put the parentheses. It
will probably be something like this:

FROM (Reviews As A INNER JOIN Book_users As B
On (A.UserID = B.UserID))
INNER JOIN Books As C On (A.BookID = C.BookID)

All queries should be designed, created and tested using the Access Query
Builder BEFORE attempting to run them from ASP. In fact, you should save
your queries, parameterizing them as necessary, and run the saved queries
from ASP rather than building them dynamically. Do a Google search for
"saved parameter queries" to find the posts I've made about creating and
running saved parameter queries.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Curt J Raddatz]

Access has some pretty good examples in the Northwind.mdb and Solutions.mdb
database that come with it. The Northwind.mdb is particularly valuable
because almost all the knowledge base articles on Access use that database
as a starting point. If you can't find these files, reinstall Access and
make sure you install all components.


"Alistair" <forget_it> wrote in message
news:109d1rffseefje7@corp.supernews.com...

>
> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
> news:eSrPiTTMEHA.3972@TK2MSFTNGP10.phx.gbl...

> > > for each result I get, I then have to take the unique identfier for

the

> > > users who have read this book and the search the users table to get

the

> > > users details..

> >
> > No, not at all. You perform what is called a JOIN. Some samples here,
> > [url]http://www.aspfaq.com/2241[/url] and also read up on general efficiency

> approaches

> > in [url]http://www.aspfaq.com/2424[/url]. And as Raydan stated, there are JOIN
> > examples in the Access help file. I don't think you should ever need or
> > want to nest recordsets in ASP. You've already sacrificed enough by
> > choosing Access in the first place. ;-)
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > [url]http://www.aspfaq.com/[/url]
> >
> >

> If I knew another databse I would use it, but my skills are limited in

this

> area.
>
> and alas the help files in Access2000 are woefully poor...not even a

sample

> database to look at.
>
> but I'm reading up on JOINS as we speak
>
>
>

[Alistair]

> In this case, no.

> Remember, the first step to take in debugging a dynamic sql statement is

to

> write it to the response:
>
> response.write strSQL
>
> Running the page and looking at the statement written to the browser

window

> will usually show you the cause of the problem. We need to see the
> response.written statement if it does not help you solve your problem.
>
> Unfortunately, in this case, the problem is not so obvious. Your

statement,

> as written, would run without flaw in SQL Server. In Jet (Access),

however,

> it's a different story. Jet is very particular about the use of

parentheses

> when joining more than 2 tables. Frankly, I find it hard myself to

remember

> where to put the parentheses. So what do I do? I open my database in

Access

> and use the Access Query Builder to create my query using the GUI Design
> View. Create a new query in Design View, add your 3 tables to the query

from

> the Choose Tables dialog, use the Properties window to assign aliases to
> each table, click and drag the fields between the field lists to create

your

> joins, then switch to SQL View to see where Access put the parentheses. It
> will probably be something like this:
>
> FROM (Reviews As A INNER JOIN Book_users As B
> On (A.UserID = B.UserID))
> INNER JOIN Books As C On (A.BookID = C.BookID)
>
> All queries should be designed, created and tested using the Access Query
> Builder BEFORE attempting to run them from ASP. In fact, you should save
> your queries, parameterizing them as necessary, and run the saved queries
> from ASP rather than building them dynamically. Do a Google search for
> "saved parameter queries" to find the posts I've made about creating and
> running saved parameter queries.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

holy crap!!!

ok..erm...I best go buy me a book on Access. I've never used a relational DB
before and so I've never come across JOINS before and never used the Query
Builder either. In fact I can't even follow what the query statement itself
is doing, INNER join Book_users as B ON etc etc

Steep learning curve ahead methinks

many thanks for your advice though, hopefully it'll make sense to me after a
few hours reading.

[Aaron Bertrand - MVP]

> ok..erm...I best go buy me a book on Access. I've never used a relational
DB

> before and so I've never come across JOINS before and never used the Query
> Builder either.

I might suggest that you skip Access and go straight to MSDE, if your
environment allows for it. You'll learn from the ground up on a aystem
based on a true RDBMS, and avoid learning bad habits and non-standard
language/syntax.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Alistair]

I would just like to say a huge thanks to Bob and Aaron for their help.

you people are phenomenally clever!!

After reading your advice and buying a book (1024 pages!!!) on Access I've
managed to create the SQL statements as required.

it looks something like this

strSQL = "SELECT books.book_title, reviews.user_review, users.user_name,
users.user_email " & _
"FROM users INNER JOIN (books INNER JOIN reviews ON books.bookID =
reviews.bookID) ON users.userID = reviews.userID " & _
"WHERE (((books.book_title)=" & "'" & book_search_string & "'" & "));"

I have to admit that I still don't quite follow what it's doing apart from
the fact that it appears to be matching the USERID and BOOKID from the
reviews table with the same in the users, and books table and therefore
bringing in the required data.

but at least it works.

thanks guys!!

[Jeff Cochran]

On Tue, 4 May 2004 21:19:34 +0100, "Alistair" <forget_it> wrote:

>I would just like to say a huge thanks to Bob and Aaron for their help.
>
>you people are phenomenally clever!!
>
>After reading your advice and buying a book (1024 pages!!!) on Access I've
>managed to create the SQL statements as required.
>
>it looks something like this
>
>strSQL = "SELECT books.book_title, reviews.user_review, users.user_name,
>users.user_email " & _
>"FROM users INNER JOIN (books INNER JOIN reviews ON books.bookID =
>reviews.bookID) ON users.userID = reviews.userID " & _
>"WHERE (((books.book_title)=" & "'" & book_search_string & "'" & "));"
>
>I have to admit that I still don't quite follow what it's doing apart from
>the fact that it appears to be matching the USERID and BOOKID from the
>reviews table with the same in the users, and books table and therefore
>bringing in the required data.
>
>but at least it works.

I wonder if now is the time to point you to the sample databases, in
particular the one entitled "Pubs". Since it's a book/author
database, looking at its design might help you out. :)

Jeff

[Alistair]

"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
news:40985f2b.196295297@msnews.microsoft.com...

> On Tue, 4 May 2004 21:19:34 +0100, "Alistair" <forget_it> wrote:
>

> >I would just like to say a huge thanks to Bob and Aaron for their help.
> >
> >you people are phenomenally clever!!
> >
> >After reading your advice and buying a book (1024 pages!!!) on Access

I've

> >managed to create the SQL statements as required.
> >
> >it looks something like this
> >
> >strSQL = "SELECT books.book_title, reviews.user_review, users.user_name,
> >users.user_email " & _
> >"FROM users INNER JOIN (books INNER JOIN reviews ON books.bookID =
> >reviews.bookID) ON users.userID = reviews.userID " & _
> >"WHERE (((books.book_title)=" & "'" & book_search_string & "'" & "));"
> >
> >I have to admit that I still don't quite follow what it's doing apart

from

> >the fact that it appears to be matching the USERID and BOOKID from the
> >reviews table with the same in the users, and books table and therefore
> >bringing in the required data.
> >
> >but at least it works.

>
> I wonder if now is the time to point you to the sample databases, in
> particular the one entitled "Pubs". Since it's a book/author
> database, looking at its design might help you out. :)
>
> Jeff

thanks Jeff, but the sample DBs are not installed on my system.

The design of the DB was fine, it was just the querying and the relational
bit that I didn't get my head round.

Thanks anyway