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

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

Answer 2

I never heard about primary and foreign used to indicate tables. They are characteristic of columns.

In the example you give:

• in Posts, the postid column is the primary key for the table Posts
• in Comments, the postid column is a regular field for the table Comments, and designates a unique row in the table Posts ; it is called the foreign key for Posts (=key of the other table).

Answer 3

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 4

For many to many relationships, such as Networks and Users, where networks can have many members, and users can belong to many networks, you'll need to introduce a third table, which can be called something like (choose whichever you find most appropriate):

• Networks_Have_Members
• Users_BelongsTo_Networks
• NetworkMembers

This table will have a composite primary key, consisting of userId and networkId. Additionally, userId will reference Users.id as a foreign key, and networkId will reference Networks.id as a foreign key.