Joining Table design question: how many should I use?

[deadpoetnsp]

@tjc4

In my last paragraph I describe duplicating Question data on Upload and then duplicating Question and Upload data on Review.

In my gut, with the proper relational database design I don’t think I should have to repeat data (though I’m struggling to figure out how as I make clear below).

And ed727 raises a good point when he says that duplicating data can lead to issues when a record is edited. If a Question Thing were modified, I’d have to trigger a cascading series of edits of all affected Uploads and Reviews. That sounds like it could be a difficult system to maintain.

I may not have explained myself properly, or probably didn’t understand your statement. You don’t need to duplicate any data. You just add the newly created thing. For example, create a new Thing of type Question. Then, add that Question to a list of Questions in Upload. So you don’t need to duplicate Question in Upload, just add the Question you already created to the list of Questions of Upload.

To give another example, let’s say you have a thing Post with an image and a description. Then you have a list called “Posts” (a list of Post) in a User. You create a new thing of type Post, and then add the newly created thing Post to the list of Posts of the User. There is no duplication: instead, you are just adding a reference to the new post in the list of the User’s posts. In other words, you don’t need to add an image and description to the User. That is already in in the Post. You just add the new Post itself to the User’s list of Posts.