Defining a primary and external table in a relationship?

Question

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

For example, if I have a table called posts, and it contains both columns of id columns, and I have a table with comments, and it contains a column called postid. Which of these tables will be primary in the relationship. I would suggest that this is a message table. I say this because it is a one-to-many relationship, and it looks like a table with one record will be primary and a table with many will be an external table.

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

Answer 1

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

External tables contain child records, those records that are associated with parent records accordingly.

So, “comment” is a child of “post”, therefore: “Post” is the parent (main in your example) “Comment” is a child (foreign in your example)

PostId values ​​must be unique in the Post table ... but the same PostId value can appear several times in the comment table (because there can be many comments for one message, comment 1 for message 1, comment 2 for message 1).

A 1-1 relationship is when two objects are peer-to-peer. that is, the Student can be a user, and the User can be a Student. Two students cannot be the same user. Two users cannot be the same student. Therefore, User-Student is 1-1.

From many to many relationships, it is best to model a table between them.

Book (main)
Author (primary)
Author's books (comparison)

BookId is unique in books (only one book can have a specific identifier)
AuthorId is unique to authors (only one author can have a specific identifier)

BookBook BookBook has both BookId and AuthorId columns, as well as maps for authors.

This relationship is modeled because the 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 the primary key for posts in the table.
• comments.comment_id is the main key for comments on the table.
• comments.post_id is the foreign key to the posts in the table.

posts will be your "primary" table, how do you think about it.

For a relationship, a lot, a lot: (there is not too much sense, but in any case)

• 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 3

I have never heard of primary and foreign used to refer to tables. They are characteristic for columns.

In the example below:

• In posts, the postid column is the primary key for the Posts table.
• in comments, the postid column is a regular field for the Comments table and indicates a unique row in the Messages table; it is called a foreign key for messages (= key of another table).

Answer 4

For many, many relationships, such as Networks and Users , where networks can have many members and users can belong to many networks, you will need to enter a third table, which can be called something like (select what you think is most suitable) :

• Networks_Have_Members
• Users_BelongsTo_Networks
• NetworkMembers

This table will contain a composite primary key consisting of userId and networkId. In addition, userId will refer to Users.id as a foreign key, and networkId will refer to Networks.id as a foreign key.