I am trying to create a db schema for a survey. The survey is used by
marketing managers to see how well a employee helped the customer. The
survey will have 10 questions.
A customer will type in their transaction number, store number of
where they purchased the product, and answer to the multiple choice
questions. Each question will have the choices:

Rate your satisfaction with the representative who assisted you.
1 2 3 4 5

Rate your satisfaction with the way you were greeted by the
representative.
1 2 3 4 5
.....

So i have the following tables:
Customers
Transactions
Customer_Transactions
Questions
Choices
Question_Choice


Process Flow:
Customer fills out everything and answers it, hits submit.
insert into customers their name, email, phone.
insert into transactions the transaction number.
insert into Customer_has_Transactions lastInsertID of customer,
LastInsertId of transaction
insert into Question_has_Choices, LastTransactionID, questionId,
ChoiceId,

when doing reports
I can pull data by store number and calculate averages for that store.

What do you think ?

here is a diagram:
http://img253.imageshack.us/my.php?image=surveybj0.png

thanks

salimk786 wrote:
> I am trying to create a db schema for a survey. The survey is used by
> marketing managers to see how well a employee helped the customer. The
> survey will have 10 questions.
> A customer will type in their transaction number, store number of
> where they purchased the product, and answer to the multiple choice
> questions. Each question will have the choices:
>
> Rate your satisfaction with the representative who assisted you.
> 1 2 3 4 5
>
> Rate your satisfaction with the way you were greeted by the
> representative.
> 1 2 3 4 5
> ....
>
> So i have the following tables:
> Customers
> Transactions
> Customer_Transactions
> Questions
> Choices
> Question_Choice
>
>
> Process Flow:
> Customer fills out everything and answers it, hits submit.
> insert into customers their name, email, phone.
> insert into transactions the transaction number.
> insert into Customer_has_Transactions lastInsertID of customer,
> LastInsertId of transaction
> insert into Question_has_Choices, LastTransactionID, questionId,
> ChoiceId,
>
> when doing reports
> I can pull data by store number and calculate averages for that store.
>
> What do you think ?
>
> here is a diagram:
> http://img253.imageshack.us/my.php?image=surveybj0.png
>
> thanks
>

Looks reasonable. I'm not sure what the "key" column is in Choices (bad
choice - don't use reserved words as column names). But you might also
want a sequence column in the Choices so you can display them in the
order you wish.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

"salimk786" <salimk786@gmail.com> wrote in message
news:9e7a7958-d4b2-41d7-921c-d20cd0704d5d@d21g2000prf.googlegroups.com...
> I am trying to create a db schema for a survey. The survey is used by
> marketing managers to see how well a employee helped the customer. The
> survey will have 10 questions.
> A customer will type in their transaction number, store number of
> where they purchased the product, and answer to the multiple choice
> questions. Each question will have the choices:
>
> Rate your satisfaction with the representative who assisted you.
> 1 2 3 4 5
>
> Rate your satisfaction with the way you were greeted by the
> representative.
> 1 2 3 4 5
> ....
>
> So i have the following tables:
> Customers
> Transactions
> Customer_Transactions
> Questions
> Choices
> Question_Choice
>
>
> Process Flow:
> Customer fills out everything and answers it, hits submit.
> insert into customers their name, email, phone.
> insert into transactions the transaction number.
> insert into Customer_has_Transactions lastInsertID of customer,
> LastInsertId of transaction
> insert into Question_has_Choices, LastTransactionID, questionId,
> ChoiceId,
>
> when doing reports
> I can pull data by store number and calculate averages for that store.
>
> What do you think ?
>
> here is a diagram:
> http://img253.imageshack.us/my.php?image=surveybj0.png
>
> thanks

From your diagram it looks like transaction number is unique across all
stores so Customer_has_Transactions needs only one key--Transaction_idTrans
(unless a transaction can be associated with more than one customer.
Likewise, Question_has_Choices requires only two keys--Transaction_idTrans
and Question_idQuestion. Sure hope you come up with better table and column
names..

"Evan Keel" <evankeel@sbcglobal.net> wrote in message
news:7x1mj.496$R84.39@newssvr25.news.prodigy.net.. .
>
> "salimk786" <salimk786@gmail.com> wrote in message
> news:9e7a7958-d4b2-41d7-921c-d20cd0704d5d@d21g2000prf.googlegroups.com...
> > I am trying to create a db schema for a survey. The survey is used by
> > marketing managers to see how well a employee helped the customer. The
> > survey will have 10 questions.
> > A customer will type in their transaction number, store number of
> > where they purchased the product, and answer to the multiple choice
> > questions. Each question will have the choices:
> >
> > Rate your satisfaction with the representative who assisted you.
> > 1 2 3 4 5
> >
> > Rate your satisfaction with the way you were greeted by the
> > representative.
> > 1 2 3 4 5
> > ....
> >
> > So i have the following tables:
> > Customers
> > Transactions
> > Customer_Transactions
> > Questions
> > Choices
> > Question_Choice
> >
> >
> > Process Flow:
> > Customer fills out everything and answers it, hits submit.
> > insert into customers their name, email, phone.
> > insert into transactions the transaction number.
> > insert into Customer_has_Transactions lastInsertID of customer,
> > LastInsertId of transaction
> > insert into Question_has_Choices, LastTransactionID, questionId,
> > ChoiceId,
> >
> > when doing reports
> > I can pull data by store number and calculate averages for that store.
> >
> > What do you think ?
> >
> > here is a diagram:
> > http://img253.imageshack.us/my.php?image=surveybj0.png
> >
> > thanks
>
> From your diagram it looks like transaction number is unique across all
> stores so Customer_has_Transactions needs only one
key--Transaction_idTrans
> (unless a transaction can be associated with more than one customer.
> Likewise, Question_has_Choices requires only two keys--Transaction_idTrans
> and Question_idQuestion. Sure hope you come up with better table and
column
> names..
>
>
>

Oops, if a transaction belongs to one customer then you don't need the
Customer_has_Transactions table.