Determining the primary and foreign table in a relationship?

Question

When designing a database, what usually determines what tables will be the primary and foreign table in a relationship?

For example, if I have a table called posts and it contains and id column postid and I have a table called comments and it contains a column called postid. Which of these tables would be the primary one in the relationship. I would assume it is the posts table. I am saying this because this is a one-to-many relationship and it seems like the table with one entry would be the primary one and the table with the many would be the foreign table.

What about many to many relationships or 1 to 1 relationships, what are the primary and foreign tables in those scenarios?

Answer 1

The primary table contains the parent records, those records that define the root records such as "posts" in this example.

The foreign tables contain child records, those records that add data to parent records in a related way.

So, a "comment" is a child of a "post", therefore: "Post" is parent (primary in your example) "Comment" is child (foreign in your example)

PostId values must be unique in the Post table... but the same PostId value can occur multiple times in the Comment table (because there might be many comments for a single post; comment 1 is for post 1, comment 2 is for post 1).

1-1 relationships are when two entities are peers. i.e. a Student may be a User and a User may be a Student. Two students cannot be the same user. Two users cannot be the same student. Therefore User - Student are 1-1.

Many to many relationships are best modeled with a table in between.

Book (primary)
Author (primary)
AuthorBooks (mapping)

BookId is unique in Books (only one book may have a certain id)
AuthorId is unique in Authors (only one author may have a certain id)

AuthorBooks has both BookId and AuthorId columns and maps books to authors.

This relationship is modeled because an author may have written many books and because a particular book may have many authors.

Answer 2

Given:

table posts
post_id

table comments
comment_id
post_id

table posts_to_comments
post_comment_id
post_id
comment_id

• posts.post_id is primary key for table posts.
• comments.comment_id is primary key for table comments.
• comments.post_id is a foreign key to table posts.

posts would be your "primary" table as you're thinking of it.

For a many-many relationship: (doesn't make too much sense here, but anyway)

• posts_to_comments.post_comment_id is primary key
• posts_to_comments.post_id is foreign key to posts
• posts_to_comments.comment_id is foreign key to comments