Data base design query

[David Beaven]

Have a customers comments table with fields e.g. : book name, date of
submission of comment, userid, comment
If my main two queries are:
How many unique users have commented on a specific book (and what were the
comments)
and how many unique users have commented on a specific book in a particular
time frame e.g. last week
Should I be splitting this data into other tables (I'm not sure if the above
table is normalised) and what sort of sql statements would acomplish these
queries?
And to make these queries faster should I consider indexing (and if so on
what field)?

Any help on this very gratefully received
David

 

[Steve Schapel]

David,

I would consider a separate Books table, with a BookID and BookName
fields. And then use the BookID rahter than BookName in the Comments
table. You mentioned speed... such a thing might increase speed of
your query, although the difference is only likely to be noticeable by
the human eye with a large amount of data. But there would be some
other potential advantages. Depending on how you are setting up your
form, it might make data entry quicker or more accurate. And it may
help with a possible problem of more than one book with the same name?

When you say "how many unique users have commented on a specific
book", it sounds like you want to allow for the possibility of any
given user making more than one comment on the same book. If so, the
answer ot your question depends a bit on what you want to do with
those comments. Do you want your query to concatenate the comments
for that person together into one unit, or do you want to retain them
as separate records, and thus count 1 user and x comments? Either
way, though, the table structure is adequate for the purpose.

An incomplete answer, but hope these comment help.

- Steve Schapel, Microsoft Access MVP