relationship between database tables not sharing any foreign key(s) in ERD

Question

Consider an example of following database tables:

user ( id [PK], user_name, ... )

comment ( id [PK], u_id [FK], ... )

reportedCommentInfo ( id [PK], c_id [FK], reporting_u_id [FK], date, ... )

admin ( id [PK], user_name, ... )

According to this database schema any user can post a comment and can report other user comments based on containing abusive or spamming material. Admin can view a list of all reported comments by users and can remove comments not complying to set standards.

In this scenario admin doesn't actually have access to any of the reported comments i.e. their id(s) are not present inside the admin table, yet admin can access and manage reported comments. So while creating ERD will their be a relation between reportedCommentInfo and admin table?

i.e. basically my question is, while creating ERD/data model can we create a relationship between two tables which do not have any primary key / foreign key relationship?

or two tables can only be related if they have primary key / foreign key relationship among them.

p.s: any suggestions for improving anything in either database schema or ERD structure is more than welcome.

Answer 1

If any admin can view any comment, there's no need to model a relationship between them. Don't confuse data modeling with system modeling. The data model only needs to model facts that you want to record. If you want to keep track of which admins reviewed which comments, then you certainly can introduce a relationship between them. However, you don't need a relationship just to give admin users access to all comments. Rather, your application code can check whether the logged-in user is an admin user or not, and depending on their status show different buttons or content.

You ask whether "two tables can only be related if they have primary key / foreign key relationship among them". In the relational and entity-relationship perspectives, we don't relate tables, we use tables to relate sets of values. Some sets represent real-world things (like users) while others represent labels (like names) or measurements (like dates). Any two or more sets of values can be related by creating a suitable table for that purpose. Foreign key constraints are used to indicate that one column (set of values) is a subset of another column (set of values), not to associate rows. For more on this topic, I recommend a book like Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars.

Answer 2

You seem to have some fundamental misunderstandings about Entity-Relationship models/diagrams and relational modeling.

Entity types/tables have box icons and relationship types/tables have diamond icons. Participations of entities in relationships are the lines/FKs. Your diagram already has a relationship & table between reported comments and admins: reviews. If you want the rows satisfying a different application relationship/association then you either have to express it in terms of given/base relationships via a query or you have to add a new given/base relationship & table.

-- REPORTEDCOMMENTID identifies reporting of comment COMMENTID ...
select * from ReportedCommentInfo

--admin ADMINID has name NAME and ...
select * from Admin   

--   REPORTEDCOMMENTID identifies reporting of comment COMMENTID ...
-- AND admin ADMINID has name NAME and ...
select * from ReportedCommentInfo join Admin    

You don't need lines/FKs to query. They're just given/base participations. (Misrepresentations of ER modeling call participations/FKs "relationships".)

PS There are tables and there are jobs. Tables record the state of the business. A job involves reading and/or updating some relevant tables. An ER diagram just shows the tables. You seem to be confusing ER diagrams with data flow diagrams, which are about how people use tables to do their job. An ER model (schema/diagram) for the data you have in your schema would have User, Admin & Comment entities, a Posts relation on users and comments and a Reports relationship on users and comments, which could be considered a Report associative entity. As to variations, including your schema, you need to pick a particular design method and follow it. Also, you haven't given justification for using the ER diagram you gave.