Q&A, Blog Site - Keep to one table or split to 2 or 3 tables?

1

Working on an idea for a (1) Question (2) Answer and (3) Blog site. For the content of each of these, I can store them all in one table with some columns applying or not applying to each of these different types with a type column to differentiate each - OR, I can separate them into two tables Q, and Answer/Blog (or other combo), or into 3 tables for each type.

There are is only a minor difference between them:

In the one table idea, the columns would look like heading / detail / type - type column would differentiate each by 'id / question' / 'blog' / 'answer' / 'qid'
QUESTION: heading, detail, type
BLOG: heading, detail, type
ANSWER: qid, detail, type (does not use heading column, but references question in qid column)

I think storing all in one table may make queries simpler where there is a relationship between them, but the table gets much larger quicker... What is a good approach to a database/table design like this with the expectation this community can grow quite large over time (10K to 100K active users)?

Some typical relationships:

  1. A would be related to Q as being the answer(s) to Q
  2. Q, A, B will all be listed on the same window with checkbox choices
  3. Answers to Q can be associated with A or B

Quantity of A will far outweigh all of them, with Q following and B the least.

Thanks for your thoughts!

2

hi

if you can .. store in 1 table .. its best ..

seperate tables with PK FK relationships is a lot more head ache to maintain

Question is how would your data be !!! ??

-- your data will go into seperate tables if your data falls into
1st normal form
2nd normal form
3rd normal form
depending on how your data is going to be ..

then for even more complicated scenarios of data ( seperate tables )
4th normal form .. 5th normal form ..6th normal form

https://www.guru99.com/database-normalization.html

3

Thank you for your input. I’ll put them all into one table!

4

Tables are cheap. Redesigning later on is a pain. One table approach would be easier but will come to bite you later on

5

one idea
is to keep and "open ended approach"
that could be scaled up depending on demand

another idea is
parallelism .. keep other approaches ( designs ) in parallel
just in case ..

6

There could be more than 1 answer to a question. Just by that statement, the one table approach breaks normalization if you want to normalize

7

what i meant .. was
having 3 different case scenarios .. designs going on in parallel
so that its easy to switch !!! in case one design goes bad

just an idea
feel free to correct me .. if i am wrong
question would be why would we do this !!!!
depends on the importance of things like ..no downtime etc etc etc

1 Like
8

During design period yes I agree. absolutely important to vet all those possible design approaches. But come inplementation time, you have to choose one. Remember its not just about sql sever, you alao have to put into consideration application that sit on top of sql sever. But then again your middle tier should be designed in a way that it is not super tightly couple woth backend design. Bit nw we would be getting another realm of architecturing.

9

So having all in one table typically points to less reliability - leading to more downtime? Would like to know what is the rationale to this thought? So then 3 separate tables lead to more reliability/uptime?

10

The answer is aways it depends.

Imagine one table with 1 question

What is the most delicious fruit?

If you had the q & a for this question in one table, how many rows will you have

11

One table.
One question - "What is the most delicious fruit" = ONE ROW.
If I answered in the same table, "Apples are the most delicious fruit" = ANOTHER ROW = 2 TOTAL
More answers, more rows.

Not sure what to glean from this... help me out!

12

So with that one table design

Create table qanda(id int, question nvarchar(50), answer nvarchar(50))

With this design that one question will be repeated as many times as there are answers. Do you see an issue with that design?

13

If you see the question update, the question will be one row and not repeated. Answers to the question will be in same table, but will reference the question by its 'qid', so no duplication. The 'type' column also separates the type (Q/B/A).

14

Ok, no idea you updated the question.
so are you saying one table per
QUESTION
BLOG and
Answers?

or 1 table for all of them?

15

This is exactly my question!
Is it better (pros/cons) to do one table for all?
Or one table for Q/B + separate table for A? (or different 2 table combination)
Or 3 separate tables, one for A, one for B, one for A?

16

3 is better

17

Doing a quick scan of the question and the replies, I'd do at least 3rd normal form for storing the data. If you want to denomalize for reporting, do a little "ETL" to preaggregate the data and then report away. If you only do the later or additional requirements come up later for different reports of the addition of new data, it'll be a bugger to do so.

For storing the original data, I'd recommend (I can't think of an exception but hate to use words like "always") that you stick with at least 3rd normal form.

2 Likes
18

I Agree with @yosiasz ,
I Think 3 tables is the best for this case.
at the first you may feel it harder to maintenance the data, but when the data is grow higher, you will feel that easier to maintenance with 3 different table.

1 Like