0

I want to create a Questions and answers application like stack overflow.

I do not know how to store multiple answers in MySQL table column.

I want to know how to store questions and multiple answers in same row in the sql table.

Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
Gowthamraj Somanathan
  • 260
  • 1
  • 4
  • 15
  • Why do you need to store multiple answers in one column? Can't you define a separate **answers** table with foreign key to the **questions** table instead? – StanislavL Aug 11 '14 at 05:59
  • 1
    Doing this violates [1NF](http://en.wikipedia.org/wiki/First_normal_form). Also search for Jaywalking anti-pattern. – Ja͢ck Aug 11 '14 at 06:01

5 Answers5

2

No...don't do it. Very bad idea IMHO. It'll be hard to do any searching and stuff. There's a reason to why we still have R'ship database models.

But if you must go down that path, you can always store it as JSON or XML, and maybe somewhere in future, Oracle will provide functionality to search JSON or XML within columns :)

rurouni88
  • 1,165
  • 5
  • 10
  • Furthermore to my point, the fundamental flaw is that you're assuming 1 question == 1 answer, and that assumption is going to really mess up your system design, because it's just not true :) – rurouni88 Aug 11 '14 at 06:09
  • In some situations, it is not a bad idea to store multiple values in a single column using XML or json. For example, in our app, we constantly add new properties for some entities. There is no requirement for searching through these properties. Adding a column in a table requires too much efforts and extensive changes in views, stored procedures and asp.net code (entity model needs to be changed, code needs to be changed). Much easier to simply add one more property in json without a need for any other changes. – Allen King Aug 11 '14 at 06:12
  • No, I complete agree. I've rolled out solutions where we've stored XML or JSON, even Storeable format in a DB column. However, they were data that did not need to be searchable, so that solution is adequate. The problem I have is the data modelling the OP is intending on using, 1 question == 1 answer, which simply won't hold up, if he intends to create a Q&A like StackOverflow :) ie. The 1 question he raised already has 4 answers at the time of this post :) – rurouni88 Aug 11 '14 at 06:15
1

You can create an object (suppose you want to save firstname and lastname in the same column, just for example), something like this (VB example):

public class myClass
public property firstname
public property lastname
end class

then create an instance and assign properties

dim cls as new myClass
cls.firstname= ...
cls.lastname = ...

Then use JavaScriptSerializer to convert this object into json string and store that string in the column. On the way back, desialize the value from the column using JavaScriptSerializer to get back an instance of myClass. Search on this site if you need information about how to use JavaScriptSerializer (it has been answered here several times, hence, not repeating).

If using SQL Server, you may also have a column of XML data type and store XML (instead of json) in the column. You can search in XML in SQL Server. See How can I query a value in SQL Server XML column. MySQL newer versions also support searching in data stored in json format.

Allen King
  • 2,372
  • 4
  • 34
  • 52
1

Creating two tables and connecting them using foreign key relationship is one of the best solutions. Because keeping questions and answers in a same table may not allow you to have multiple answers for the same questions. If you want a single question to have multiple answers then the redundancy of your DB will surely increase. So, try to use DB-Relationship concepts properly.

VaibhavR
  • 28
  • 6
0

If you need StackOverflow Structure you should to create two tables, Questions and Answers and relate them together with a foreign key from Answers' to 'Questions

Farrokh
  • 1,167
  • 1
  • 7
  • 18
0

By reading your Question, I realized, from my point of view, that your skills may not be good enough to do and to understand what you want to do.

So I´d like to suggest you to watch this Tutorial by Bucky Roberts on YouTube and to read this Wikipedia Article about Data Redundancy.

Anyways to answer your Question.

I would not store answers and questions all in a single table column. Think about creating a second table called 'answers', give an ID to the answers and connect them with the table'questions'.

This is called the Foreign Key Concept. You can read about it here.

CodeFanatic
  • 11,434
  • 1
  • 20
  • 38