DB design question

I asked this once before, but I dont think I was very clear, so here is try
number 2:

I have a DB with a basic users table and a table which keeps track of
"challenges" (we do a smoking challenge and an environmental challenge, and
a fitness challenge, etc... for young people)

USER_TBL
user_id
firstname
lastname
....

CHALLENGE_TBL
challenge_id
name
year
....

For each challenge we want to gather some extra info from teh participating
users: "are you a smoker?" "do you recylce?" The questions vary from
challenge to challenge as does teh number of questions.

As I see it, I have 2 options an none of them seem very good:

OPTION 1: create a new table for each unique challenge. For example, a
table like this:

SMOKING_CHALLENGE_2004_INFO_TBL
challenge_id
user_id
isSmoker
yearsSmoking
hasTriedQuitting
....

This is not practical because it means editing the DB every time there is a
new challenge and creating (over several years) a huge number of tables to
manage.

OPTION 2: create one generic table like this:
CHALLENGE_EXTRA_INFO_TBL
challenge_id
user_id
answer1
answer2
answer3
answer4
....
answer10

The problem here is that (a) most challenges will have fewer than 10
questions, (b) I have to keep track of what question corresponds to what
answer for each challenge somewhere, (c) I am limiting the number of
questions per challenge to 10


Can someone give me a good solution here. Thanks!
Alex

Well, I can give you a solution. Whether or not it's good depends on
whether or not you like it.

Since you don't know how "wide" your rows will be, design your database to
go "deep" instead.

USERS (UserID, FirstName, LastName, ...)
CHALLENGES(ChallengeID, Name, Year, CreatedBy, DateAdded, ...)
CHAL_QUES(ChallengeID, QuestionNo, Question)
CHAL_ANS(ChallengeID, UserID, QuestionNo, Answer)

This will allow you to have any number of questions and answers in your
challenges.

"Alexander Ross" <aross (AT) dosomething (DOT) org> wrote

I asked this once before, but I dont think I was very clear, so here is
try
number 2:

I have a DB with a basic users table and a table which keeps track of
"challenges" (we do a smoking challenge and an environmental challenge,
and
a fitness challenge, etc... for young people)

USER_TBL
user_id
firstname
lastname
...

CHALLENGE_TBL
challenge_id
name
year
...

For each challenge we want to gather some extra info from teh
participating
users: "are you a smoker?" "do you recylce?" The questions vary from
challenge to challenge as does teh number of questions.

As I see it, I have 2 options an none of them seem very good:

OPTION 1: create a new table for each unique challenge. For example, a
table like this:

SMOKING_CHALLENGE_2004_INFO_TBL
challenge_id
user_id
isSmoker
yearsSmoking
hasTriedQuitting
...

This is not practical because it means editing the DB every time there is
a
new challenge and creating (over several years) a huge number of tables to
manage.

OPTION 2: create one generic table like this:
CHALLENGE_EXTRA_INFO_TBL
challenge_id
user_id
answer1
answer2
answer3
answer4
...
answer10

The problem here is that (a) most challenges will have fewer than 10
questions, (b) I have to keep track of what question corresponds to what
answer for each challenge somewhere, (c) I am limiting the number of
questions per challenge to 10


Can someone give me a good solution here. Thanks!
Alex

I thought of that, but that solution means that the number of rows in the
chal_ans table will grow too quickly ....


"CMBergin" <NoHarvestForYou (AT) NoSpam (DOT) org> wrote

Well, I can give you a solution. Whether or not it's good depends on
whether or not you like it.

Since you don't know how "wide" your rows will be, design your database to
go "deep" instead.

USERS (UserID, FirstName, LastName, ...)
CHALLENGES(ChallengeID, Name, Year, CreatedBy, DateAdded, ...)
CHAL_QUES(ChallengeID, QuestionNo, Question)
CHAL_ANS(ChallengeID, UserID, QuestionNo, Answer)

This will allow you to have any number of questions and answers in your
challenges.

"Alexander Ross" <aross (AT) dosomething (DOT) org> wrote in message
news:cm8u77$mqc$1 (AT) forums (DOT) macromedia.com...
I asked this once before, but I dont think I was very clear, so here is
try
number 2:

I have a DB with a basic users table and a table which keeps track of
"challenges" (we do a smoking challenge and an environmental challenge,
and
a fitness challenge, etc... for young people)

USER_TBL
user_id
firstname
lastname
...

CHALLENGE_TBL
challenge_id
name
year
...

For each challenge we want to gather some extra info from teh
participating
users: "are you a smoker?" "do you recylce?" The questions vary from
challenge to challenge as does teh number of questions.

As I see it, I have 2 options an none of them seem very good:

OPTION 1: create a new table for each unique challenge. For example, a
table like this:

SMOKING_CHALLENGE_2004_INFO_TBL
challenge_id
user_id
isSmoker
yearsSmoking
hasTriedQuitting
...

This is not practical because it means editing the DB every time there
is
a
new challenge and creating (over several years) a huge number of tables
to
manage.

OPTION 2: create one generic table like this:
CHALLENGE_EXTRA_INFO_TBL
challenge_id
user_id
answer1
answer2
answer3
answer4
...
answer10

The problem here is that (a) most challenges will have fewer than 10
questions, (b) I have to keep track of what question corresponds to what
answer for each challenge somewhere, (c) I am limiting the number of
questions per challenge to 10


Can someone give me a good solution here. Thanks!
Alex

Too quickly meaning what? Who decided how quickly it can grow? Oh, you
must be using Access. Sorry.

Well, in that case, you're pretty much stuck just creating a bunch of
useless columns:
USERS(UserID, FirstName, LastName, etc)
CHALLENGES(ChallengeID, Name, DateAdded, ..., Question1, ... , Question50)
ANSWERS(UserID, ChallengeID, Answer1, ..., Answer50)

Now, of course, each question/answer pair corresponds. You just have to
live with all the NULLs for shorter challenges. Sorry. Especially since
that looks like your original solution, so that means I haven't helped at
all.


"Alexander Ross" <aross (AT) dosomething (DOT) org> wrote

I thought of that, but that solution means that the number of rows in the
chal_ans table will grow too quickly ....


"CMBergin" <NoHarvestForYou (AT) NoSpam (DOT) org> wrote in message
news:cm8vcb$nr5$1 (AT) forums (DOT) macromedia.com...
Well, I can give you a solution. Whether or not it's good depends on
whether or not you like it.

Since you don't know how "wide" your rows will be, design your database
to
go "deep" instead.

USERS (UserID, FirstName, LastName, ...)
CHALLENGES(ChallengeID, Name, Year, CreatedBy, DateAdded, ...)
CHAL_QUES(ChallengeID, QuestionNo, Question)
CHAL_ANS(ChallengeID, UserID, QuestionNo, Answer)

This will allow you to have any number of questions and answers in your
challenges.

"Alexander Ross" <aross (AT) dosomething (DOT) org> wrote in message
news:cm8u77$mqc$1 (AT) forums (DOT) macromedia.com...
I asked this once before, but I dont think I was very clear, so here
is
try
number 2:

I have a DB with a basic users table and a table which keeps track of
"challenges" (we do a smoking challenge and an environmental
challenge,
and
a fitness challenge, etc... for young people)

USER_TBL
user_id
firstname
lastname
...

CHALLENGE_TBL
challenge_id
name
year
...

For each challenge we want to gather some extra info from teh
participating
users: "are you a smoker?" "do you recylce?" The questions vary from
challenge to challenge as does teh number of questions.

As I see it, I have 2 options an none of them seem very good:

OPTION 1: create a new table for each unique challenge. For example,
a
table like this:

SMOKING_CHALLENGE_2004_INFO_TBL
challenge_id
user_id
isSmoker
yearsSmoking
hasTriedQuitting
...

This is not practical because it means editing the DB every time there
is
a
new challenge and creating (over several years) a huge number of
tables
to
manage.

OPTION 2: create one generic table like this:
CHALLENGE_EXTRA_INFO_TBL
challenge_id
user_id
answer1
answer2
answer3
answer4
...
answer10

The problem here is that (a) most challenges will have fewer than 10
questions, (b) I have to keep track of what question corresponds to
what
answer for each challenge somewhere, (c) I am limiting the number of
questions per challenge to 10


Can someone give me a good solution here. Thanks!
Alex

.oO(Alexander Ross)

I thought of that, but that solution means that the number of rows in the
chal_ans table will grow too quickly ....

You have to store the answers somewhere. Either in separate tables (one
for each challenge, which is impractical and bad design) or all in one.
The required storage space should be similar, but the latter is more
flexible. And even if the table contains thousands of answers - who
cares? The database can handle that, it is designed to maintain large
amounts of data.

Micha

Im not using access ...

I mean that using this technique, if we have 1000 users answer 10 questions
for a single challenge, that is 10,000 rows of data (instead of just 1000)
Over the next year we project 15000-20000 users participating in 6
challenges. if each has 10 questions and we expand our challenges for next
year (as planned) then the tables will hit 1,000,000 redords within three
years. At some point this will simply be too big, will it not?

Alex

"CMBergin" <NoHarvestForYou (AT) NoSpam (DOT) org> wrote

Too quickly meaning what? Who decided how quickly it can grow? Oh, you
must be using Access. Sorry.

Well, in that case, you're pretty much stuck just creating a bunch of
useless columns:
USERS(UserID, FirstName, LastName, etc)
CHALLENGES(ChallengeID, Name, DateAdded, ..., Question1, ... , Question50)
ANSWERS(UserID, ChallengeID, Answer1, ..., Answer50)

Now, of course, each question/answer pair corresponds. You just have to
live with all the NULLs for shorter challenges. Sorry. Especially since
that looks like your original solution, so that means I haven't helped at
all.


"Alexander Ross" <aross (AT) dosomething (DOT) org> wrote in message
news:cm91bp$plt$1 (AT) forums (DOT) macromedia.com...
I thought of that, but that solution means that the number of rows in the
chal_ans table will grow too quickly ....


"CMBergin" <NoHarvestForYou (AT) NoSpam (DOT) org> wrote in message
news:cm8vcb$nr5$1 (AT) forums (DOT) macromedia.com...
Well, I can give you a solution. Whether or not it's good depends on
whether or not you like it.

Since you don't know how "wide" your rows will be, design your database
to
go "deep" instead.

USERS (UserID, FirstName, LastName, ...)
CHALLENGES(ChallengeID, Name, Year, CreatedBy, DateAdded, ...)
CHAL_QUES(ChallengeID, QuestionNo, Question)
CHAL_ANS(ChallengeID, UserID, QuestionNo, Answer)

This will allow you to have any number of questions and answers in your
challenges.

"Alexander Ross" <aross (AT) dosomething (DOT) org> wrote in message
news:cm8u77$mqc$1 (AT) forums (DOT) macromedia.com...
I asked this once before, but I dont think I was very clear, so here
is
try
number 2:

I have a DB with a basic users table and a table which keeps track of
"challenges" (we do a smoking challenge and an environmental
challenge,
and
a fitness challenge, etc... for young people)

USER_TBL
user_id
firstname
lastname
...

CHALLENGE_TBL
challenge_id
name
year
...

For each challenge we want to gather some extra info from teh
participating
users: "are you a smoker?" "do you recylce?" The questions vary from
challenge to challenge as does teh number of questions.

As I see it, I have 2 options an none of them seem very good:

OPTION 1: create a new table for each unique challenge. For example,
a
table like this:

SMOKING_CHALLENGE_2004_INFO_TBL
challenge_id
user_id
isSmoker
yearsSmoking
hasTriedQuitting
...

This is not practical because it means editing the DB every time
there
is
a
new challenge and creating (over several years) a huge number of
tables
to
manage.

OPTION 2: create one generic table like this:
CHALLENGE_EXTRA_INFO_TBL
challenge_id
user_id
answer1
answer2
answer3
answer4
...
answer10

The problem here is that (a) most challenges will have fewer than 10
questions, (b) I have to keep track of what question corresponds to
what
answer for each challenge somewhere, (c) I am limiting the number of
questions per challenge to 10


Can someone give me a good solution here. Thanks!
Alex

.oO(Alexander Ross)

I mean that using this technique, if we have 1000 users answer 10 questions
for a single challenge, that is 10,000 rows of data (instead of just 1000)

So you have either

* 10.000 rows, each with one answer or
* 1.000 rows, each with 10 answers

I don't think the first will require more storage space. Additionally
you're more flexible, even if the table gets very long.

Over the next year we project 15000-20000 users participating in 6
challenges. if each has 10 questions and we expand our challenges for next
year (as planned) then the tables will hit 1,000,000 redords within three
years. At some point this will simply be too big, will it not?

I don't think so. MySQL is able to handle millions of records, other
DBMS should be capable of that as well.

Another idea: You could use separate question/answer tables per year:

CHAL_QUES_2004
CHAL_ANS_2004
CHAL_QUES_2005
CHAL_ANS_2005
....

This way you won't end up with one huge table. But you might run into
trouble if you want to process the collected data, for example if you
want an overview of all previous challenges and their questions. This
would require a query over an unknown number of tables, so you would
have to build the query dynamically with a script and join the tables
together (UNION or something like that).

With one table for the questions it would be a simple query on just two
tables instead.

Having multiple tables with the same structure and same type of
informations might be a solution in some cases, but often they cause
more troubles than a long single table.

Micha