Organizing user-input n user-input on user-input in SQL

First a little bit of backstory:

I am creating a website (along with my teammates) where users have to be able to create 'concepts' (which consisits of a timestamp, user who created it, title, description, summary, and alot more). On said concepts people have to be able to post 'ideas' (timestamp, ip of creator, text). And lastly, people have to be able to post comments on ideas (which consists of the same things as ideas).

tl;dr: we are building a site with an infrastructure similar to StackOverflow: Which has 3 tiers (for lack of a better word) of user-input: questions, answers to questions, and comments on those answers.

Originally, what me and my teammates were considering doing was having one table for all the concepts, then each concept would also have a table titled <the concept's id in the table>_ideas Then each idea would get a row in that table and another table titled <the concept's id in the table>_<idea id in the concepts's id table>_ideas however some quick testing of this showed that we were getting spammed with tables...

So we need to restructure our current SQL, our current idea was simply to combine the table for ideas and comments and make an extra field in that table which denoted whether the row was an idea or comment.

However, I wanted to throw the question开发者_StackOverflow中文版 out to stack overflow before we did anything:

What is the best (by which I mean fastest) way to organize the three teirs of SQL data?

each concept would also have a table

tables are not meant to be dynamic elements. of course they can be, but seriously.

a relational database structure has a static amount of tables.

please read about the fundamentals of relational database design

the question whether you combine different entities in one table, and add a field to distinguish them, is very valid one.

in the end i think it comes down to the application. it can seriously speed up things but also slow down. i prefer seperate tables because you save one index and may have it easier with sharding in fugure. UNION statements are cheap.

that being said. start with fixed objects, create tables for them.

create a table for "concepts" one for "ideas" one for "comments" one for "users" etc...

comments get a field for "idea" which idea they belong to etc.

Use database relations. Your three tables are concept, idea, and comment. Each concept has many ideas, and each idea has many comments (though I find it more intuitive so say "each idea is related to one concept, each comment is related to one idea").

Your tables will look something like:

concept: id timestamp title user_id
idea: id timestamp ip text concept_id user_id
comment: id text idea_id comment_id
user: id username ....

Your id fields are primary auto_incrementing keys (you can set this in phpMyAdmin). So for example, your idea_id in the comment table will relate a comment to an idea. To select all comments for idea 10: SELECT * FROM comment WHERE idea_id=10

You can use SQL join queries to do more complicated stuff.

Generally, the way to implement topics/responses/subcomment type of structure is to have three tables with foreign keys:

1. Concepts table:
• Primary key
• Name
• timestamp
• user_id
• ... whatever
2. ideas table:
• Primary key
• Key which references Concept table primary key
• Name
• timestamp
• user_id
• ... whatever
3. Comments table:
• Primary key
• Key which references Ideas table primary key
• Key which references Concepts table primary key (optional)
• Name
• timestamp
• user_id
• ... whatever

I would use three different tables with foreign keys to link them. But you may also nest comments (or whatever) by having a 'parent'-row on the comment-table, which is a foreign-key to this tables primary key.

CREATE TABLE IF NOT EXISTS `comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_comment` int(11) DEFAULT NULL COMMENT 'allows commenting on comments (annotations)',
  `user` int(11) NOT NULL,
  `headline` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_comment_parent` (`parent_comment`),
  KEY `fk_comment_user` (`user`)
);

ALTER TABLE `comment`
  ADD CONSTRAINT `fk_comment_parent` FOREIGN KEY (`parent_comment`) REFERENCES `comment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_comment_user` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON UPDATE CASCADE;

Just 3 tables, concepts, ideas, and comments, with a field referencing the related 'parent'. Using the proper indexes will mean they will be as fast, or actually due to in-memory-caching of tables, open file descriptors, cross-concepts-queries etc., faster then any multi-table setup. Rule of thumb: if there is data/information in the table name, something is very wrong.

You're looking for "foreign key references." You will likely find yourself with three tables: questions, answers, and comments on answers. Answers will have a field referencing the id of the question it's responding to, and likewise you will have a field in the comments table referencing the id of the answer it's responding to. I hope these links can be helpful:

Wikipedia

w3c

This is an important area of database design that requires some heavier reading than it sounds like you're expecting.