Table Design- one or many?

[kimi]

I have just started working on a project that is partially complete. It
is an application that is using access to store test results. The test
results are being stored in two Access 2000 databases.

DB #1 = StudentDB
DB #2 = TestResulstsDB

Why are there 2 dbs? I do not know - but that is one of the tings that
we will be changing. Combining all of the data into one database.

But the current design is what I am concerned about. By no means am I an
expert - nor have I had any formal training in Programming or DB Design.
So pardon me if the question is elementary.

I feel I have a decent handle on good db design so when I realized what
was going on here - I was a bit concerned.

Ok like I mentioned above there is a Students DB and a TestResults DB
(not two tables in one databases - but two databases )

When a student takes a test the results are stored in a dynamically
built table in TestResults DB.
So if the student takes 5 tests there are 5 corresponding tables for
this student - one for each test.

Several questions - comments/concerns

#1 My instincts tell me this design will be an absolute nightmare when
trying to retrieve test results. Am I off base here?

#2 Some of the tests will have 600 records per test that will need to be
stored. So a single table with many test results could grow quickly.
Each record consists of four fields of numeric data. This is not a web
app - this is a standalone application and it will be the only app
running on this machine. With maybe one other networked machine.
How many records will be to many records in one table?

#3 There will be multiple tests with different sets of results. Would it
be beneficial to design one table per set of results or one table with
all results?
Where for each test type only some of the fields will have data -
leaving many null values in the results that are not related to the test
in question.

testresults1 testresults2
fieldA number fieldE int
fieldB number fieldF varchar(35)
fieldC number fieldG number
fieldD number
OR

testresults
fieldA number
fieldB number
fieldC number
fieldD number
fieldE int
fieldF varchar(35)
fieldG number
Thank you in advance for your help.
Kim




Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[PC Datasheet]

<< Why are there 2 dbs? I do not know - but that is one of the tings that we
will be changing. >>

I don't recommend you do this unless you have checked closely on why it was done
that way in the beginning. My guess is that some other bigger system generates
the student database because the students change from year to year. The old
adage is don't fix something that ain't broken.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com
"kimi" <ki*@kimmyXSPAM X.com> wrote in message
news:40******** *************** @news.frii.net. ..

I have just started working on a project that is partially complete. It
is an application that is using access to store test results. The test
results are being stored in two Access 2000 databases.

DB #1 = StudentDB
DB #2 = TestResulstsDB

Why are there 2 dbs? I do not know - but that is one of the tings that
we will be changing. Combining all of the data into one database.

But the current design is what I am concerned about. By no means am I an
expert - nor have I had any formal training in Programming or DB Design.
So pardon me if the question is elementary.

I feel I have a decent handle on good db design so when I realized what
was going on here - I was a bit concerned.

Ok like I mentioned above there is a Students DB and a TestResults DB
(not two tables in one databases - but two databases )

When a student takes a test the results are stored in a dynamically
built table in TestResults DB.
So if the student takes 5 tests there are 5 corresponding tables for
this student - one for each test.

Several questions - comments/concerns

#1 My instincts tell me this design will be an absolute nightmare when
trying to retrieve test results. Am I off base here?

#2 Some of the tests will have 600 records per test that will need to be
stored. So a single table with many test results could grow quickly.
Each record consists of four fields of numeric data. This is not a web
app - this is a standalone application and it will be the only app
running on this machine. With maybe one other networked machine.
How many records will be to many records in one table?

#3 There will be multiple tests with different sets of results. Would it
be beneficial to design one table per set of results or one table with
all results?
Where for each test type only some of the fields will have data -
leaving many null values in the results that are not related to the test
in question.

testresults1 testresults2
fieldA number fieldE int
fieldB number fieldF varchar(35)
fieldC number fieldG number
fieldD number
OR

testresults
fieldA number
fieldB number
fieldC number
fieldD number
fieldE int
fieldF varchar(35)
fieldG number
Thank you in advance for your help.
Kim




Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[kimi]

<<<< Why are there 2 dbs? I do not know - but that is one of the tings
that we
will be changing. >>

<<I don't recommend you do this unless you have checked <<closely on why
it was done
<<that way in the beginning. My guess is that some other <<bigger system
generates
<<the student database because the students change from <<year to year.
The old
<<adage is don't fix something that ain't broken.
Hello Thank you for replying.
Perhaps my answer to this questions was a tad flippant - and it was not
meant to be. I will completely understand the entire current design -
before any design changes are made. It is my job to re-design the
database(s). The app(s) areall in development and will required some
re-design to handle the new db(s) design.

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

"kimi" wrote

DB #1 = StudentDB
DB #2 = TestResulstsDB

Why are there 2 dbs? I do not know -
but that is one of the tings that we will
be changing. Combining all of the data
into one database.

But the current design is what I am concerned about. By no means am I an
expert - nor have I had any formal training in Programming or DB Design.
So pardon me if the question is elementary.

I feel I have a decent handle on good db design so when I realized what
was going on here - I was a bit concerned.

Ok like I mentioned above there is a Students DB and a TestResults DB
(not two tables in one databases - but two databases )

When a student takes a test the results are stored in a dynamically
built table in TestResults DB.
So if the student takes 5 tests there are 5 corresponding tables for
this student - one for each test.

Several questions - comments/concerns

#1 My instincts tell me this design will
be an absolute nightmare when trying
to retrieve test results. Am I off base here?
You are absolutely correct. Creating multiple tables per test per student is
going to make it worse than any nightmare you've ever imagined when you need
to retrieve the information and do something with it -- a classic case of
using the tablename itself instead of data.
#2 Some of the tests will have 600 records
per test that will need to be stored. So a
single table with many test results could
grow quickly. Each record consists of four
fields of numeric data. This is not a web
app - this is a standalone application and it
will be the only app running on this machine.
With maybe one other networked machine.
How many records will be to many records
in one table?
Many people report here very acceptable performance with hundreds of
thousands or millions of records.
#3 There will be multiple tests with
different sets of results. Would it
be beneficial to design one table per
set of results or one table with all results?
Where for each test type only some of
the fields will have data - leaving many
null values in the results that are not related
to the test in question.

I don't think we have enough information to answer this question. In
general, it would probably be best to have just one table, but specific
details about the information you are keeping and how you will be using it
could indicate a different design for the specific situation.

Larry Linson
Microsoft Access MVP

[kimi]

Hi Larry Thank You for replying.

a classic case of using the tablename itself instead of data.
What do you mean using the tablename itself instead of the data?

Many people report here very acceptable performance with >>hundreds of thousands or millions of records.

Is there a defined limit where performance declines?
I don't think we have enough information to answer this >>question. In
general, it would probably be best to have just one >>table, but specificdetails about the information you are keeping and how you >>will be using itcould indicate a different design for the specific >>situation.

I don't think I have enough info yet - to ask this question. If that
makes sense. I am getting a better feel for the stat that needs to be
stored - when I have a complete understanding I will re-ask the
question.
Thank you for your time.

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[kimi]

Here is a link to the proposed design for the table of test results.

http://www.kimmy.com/sampletable.html

Any reason why this data cannot be stored in the same table?

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pieter Linden]

kimi <ki*@kimmyXSPAM X.com> wrote in message news:<40******* *************** *@news.frii.net >...

Here is a link to the proposed design for the table of test results.

http://www.kimmy.com/sampletable.html

Any reason why this data cannot be stored in the same table?

Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

There's really no reason why most things can't be stored in a table...
it's more usually how to *best* store them to make the tools that
SQL/Access gives you to manipulate them easily work best. So what may
look like efficient "human" design is actually database design.

Why not build your table like this: (TestNo,Questio nID,AnswerID) or
something like it? What exactly is this that you are modeling? Test
results of what? What are the rules? Is this like a survey? If so,
look up "Survey"+"K eri Hardwick" in this NG. She had some interesting
comments about it from a while back, but still very relevant. Another
question: what are you going to DO with the data? When it all comes
down to it, if you can't get the information you want out of your
database, then it's pretty much worthless. So I would start by making
sure that getting answers out of the database is a case of writing
reasonably simple queries. If you have to go through some major
effort to get an answer that should be simple, then you definitely
have a design problem.

Okay, so to rephrase Larry's question: What *exactly* are you
modeling, anyway? Are these exam scores, drug test results, what? And
*how* do you need to manipulate them? What is the business logic
behind this stuff? As yet, that's a question only you can answer.
Until you explain that, I don't think anybody can come up with a
conclusive answer to your problem, because you will not have given
sufficient information to solve it.

Hope this helps some.
Pieter

[kimi]

Hi Pieter,
Thank you for replying.

Why not build your table like this: >>(TestNo,Quest ionID,AnswerID) This design would not meet my needs..

orsomething like it? What exactly is this that you are >>modeling? Test
results of what? What are the rules?
Understood - I need to give more info.
The project is a piece of s/w for a medical device that will test
patients in a rehab center. The test is for balance. This is not a
question and answer per se. It is more of a test with parameters like
Speed, length of test(30 sec, 40 sec, etc), PSI, etc. There are also
four numeric fields (Q1,Q2,Q3,Q4 which equal four quadrants of a
circle.) these fields derive their values from the computation of data
in a 2nd table. The data in the 2nd table is raw test data and consists
of four numeric fields with X Y corodinates.
Is this like a >>survey? If so,
look up "Survey"+"K eri Hardwick" in this NG. She had some
interesting
comments about it from a while back, but still very >>relevant. This is not a survey but I will look at the article.

Anotherquestion: what are you going to DO with the data? When it >>all comes
down to it, if you can't get the information you want out >>of your
database, then it's pretty much worthless. So I would >>start by makingsure that getting answers out of the database is a case >>of writing
reasonably simple queries. If you have to go through some >>major
effort to get an answer that should be simple, then you >>definitely
have a design problem.

Agreed - I have faced this problem in the past. Garbage in Garbage out.

This software will keep a history of a patients tests. Reports will need
to be run to show that this system is helping people in therapy. In
otherwords their test results are improving over time - or not
improving.

I cannot completely answer what determines improvement. I think it will
be the the computed values of Q1, Q2, Q3, Q4 . I understand that this is
a key component of hwo to design the db.

I am still in the process of learning what they want and need. forgive
me if I have missed something.
If you can offer any opion on design it would be most appreciated.
Thank you in advance.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[kimi]

On a second note - my original question was regarding the different data
stored in the table.

There are approximately a dozen tests - with half of the tests requiring
all of the fields for storage - where the 2nd half of tests only need
one field for a compiled score.
is there a problem storing both of these test results in the same table.
I will have 90% of the fields null for half the test types.

In the future there will be more tests added. I am going to assume that
these tests may use some of the same fields and will require other
fields to be added.

Am I better off dividing the test types based on similar test results?
Please Reply to the Newsgroups..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Roger]

this is what I see so far... what is missing ?

testInfo
testId autonumber (pk)
testName text(30)
speed
duration
pressure

patientInfo
patientId autonumber (pk)
patientName text