-1

So, I'm building a message board type website. I've got top level posts, which are stored in a posts table, and comments to those posts, which are stored in a comments table.

When you make a post (thread or comment) it's ID increments, however right now the comments and the posts have different IDs that increment on their own. Like, there can be a post #65 and a comment #65.

I'd like to make it so they share the same ID. For example, if someone makes a post and it's #65, the next post, comment or thread, will be #66.

I tried googling around, but I can't think of how to word this question, if anyone can help I would really appreciate it!

Treedot
  • 101
  • 1
  • 10
  • 2
    My question to you is, why? – Jonnix Jun 18 '16 at 22:04
  • Add another table, `post_or_comment`. You add a row to this to assign the ID, then use that ID in the `posts` or `comments` table. – Barmar Jun 18 '16 at 22:04
  • I will try this out. Thank you! @JonStirling why not? – Treedot Jun 18 '16 at 22:05
  • 1
    @Treedot Because it makes no sense that comments and posts should have the same ID? It should make zero difference to the user what the ID of either is. I mean, what problem are you trying to solve with this? – Jonnix Jun 18 '16 at 22:06
  • Because all posts are essentially the same, they look the same and behave the same. It's a message board. This system is in place on every forum in existence, top level posts and replies are all counted with the same ID. – Treedot Jun 18 '16 at 22:11
  • Yes, and no. They all have a link to the original post / a parent comment (a foreign key), but every post/comment/whatever still has its own unique ID (unique to the table). This is how relational data works. Are foreign keys actually what you're trying to get at? – Jonnix Jun 18 '16 at 22:12
  • I suppose, kind of? All posts and comments have their own IDs in the tables, but I'm looking for there to be another ID number that is basically a count of all of them, so that number can be displayed. – Treedot Jun 18 '16 at 22:18
  • Pretty sure you're talking about foreign keys then. You want to keep the data separate (so multiple tables), but want them to be related, so e,g, the comment table should have e.g. a `post_id` column containing the ID of the post it belongs to, then you can JOIN the tables and get counts and such. – Jonnix Jun 18 '16 at 22:20
  • 1
    Okay, thank you. Knowing what I'm searching for, I can now help myself. – Treedot Jun 18 '16 at 22:22
  • You can simulate a sequence. Look here http://stackoverflow.com/questions/26578313/how-do-i-create-a-sequence-in-mysql But you shouldn't use it as ID. To avoid misconcep call this column COUNTER or something similar. – Marco A. Hernandez Jun 18 '16 at 22:24

1 Answers1

1

Add a column to Your comments table, and name it - for example - reply_to - the column should contain the top post's ID to which the comment replies to... If You use innoDb don't forget to add a foreign key there: 

    alter table comments add foreign key(`reply_to`) references posts(`id`);

Good Luck!

mlattari
  • 135
  • 2
  • 10