Saving known and unknown fields in db

Question

I have a form on a page.

The form contains of 3 known fields: Sex Age StoreId

and an unknown number of fields that all begin with "Question_" followed by an ID

I would like to store these fields in my table(MyTable) in db(MyDb). Each record I need stored contains: ID(Int), Sex(String), Age(Int), StoreID(Int), QuestionID(Int), Answer(Int)

Can someone help me get started?

I got:

public ActionResult Index(Questions model, FormCollection form) {

        var Age = form["age"];
        var StoreId = form["storeId"];
        var Sex = form["sex"];

and then I guess i will be using an array getting the last "Questions_" ready for the insert?

Wrong. your table should have only the first 4 columns and then put your question and answers in separate tables joined by the questionID and the ID — Steve
– Steve, Commented Jul 29, 2016 at 10:18

anand · Accepted Answer · 2016-07-29 10:16:33Z

0

I can be done.
1st make table one for known fields and other to store the unknown fields(question and Answer) along with the first table primary id(foreign key exactly).
2nd. store the record accordingly and through relation crud operation can be performed.

answered Jul 29, 2016 at 10:16

anand

1,5895 gold badges21 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JonesDr Over a year ago

Thank you all. 2 tables... got it!

JonesDr Over a year ago

But how do I programmatically go around this: public ActionResult Index(Questions model, FormCollection form) { var Age = form["age"]; var StoreId = form["storeId"]; var Sex = form["sex"]; and then I guess i will be using an array getting the last "Questions_" ready for the insert?

anand Over a year ago

refer this link (stackoverflow.com/questions/23125087/…)

Murray Foxcroft · Accepted Answer · 2016-07-29 10:18:04Z

Sure. Create two tables. One for storing the known columns and the other for the variable questions.

The first would look like this (say Person)

personPrimaryKey(Int), Sex(String), Age(Int), StoreID(Int)

And the second as follows (say QA)

qaPrimaryKey(Int), personPrimaryKey(Int), QuestionID(Int), Answer(Int)

Then, when you need to insert, you insert Sex, Age and StoreID in to the Person table and this will give you a primary key back for the personPrimaryKey.

Then insert all the questions and answers against the personPrimaryKey in to the QA table. This allows you to have as many or few questions and answers per person.

The construct to query this back is called a SQL Join which you need to read up on.

Collectives™ on Stack Overflow

Saving known and unknown fields in db

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related