Cardinality of recursive relationship?

Question

I have created a database for Computing in Schools (CIS) and i have some extra requirements to add from the below information. I created an ERD but feel like i have made a few mistakes such as the cardinality of the recursive relationship?

Any other feedback would be appreciated.

New Requirements CiS would like to use the database to share the details of sessions, participants, etc., online through a web site. Anyone involved – student volunteer, school staff, SHU lecturers – will be able to login with a username (or maybe an email address) and a password. Once logged in, members use the site differently (school staff to request sessions, SHU lecturers to manage them, and student volunteers to see what they can take part in, confirm their availability, and check afterwards that the hours are right). CiS want to be able to share resources via the site. Resources are files that are useful in sessions or generally to CiS’ purpose. Members who create such files upload them, and the database holds such details as the URL, the file title, a description, and keep track of the file’s author. Members can upload files, members can tag them, give them a star rating, and comment them.

• Tags are keywords that users assign to a file. A file can have multiple tags; once someone has tagged a file with a keyword, there is no need for anyone else to mark the same file with the same keyword again.

• Star rating. Any site member can rate any file, but not re-rate a file that they have rated.

• Comments have no such limits, because ultimately they form a discussion about each file, so site members can write many comments about the files. It will be helpful to know the date, author and subject of every file comment.

Answer 1

Your diagram is not an ERD. In particular, the use of lines to represent relationships restricts you to binary relationships, and loses much of the expressive power of the Entity-Relationship model.

By using the combination of Username and Password as primary keys in your users tables, you allow the possibility that different users can use the same Username with different passwords. Is this what you want? However, in the Upload file table, you only store the Author_username, so perhaps you want only the Username as key? You need to be consistent here.

Your Uploaded file table needs to be broken into many. At the moment, it supports only one tag, one rating and one comment per file/username. It's not clear whether the Author_username indicates the user who uploaded the file or the user that tagged, rated and commented on a file. BTW, those things are linked together in your table, preventing a user from posting more tags or comments than ratings. The File title and File description are probably dependent only on File URL and will be duplicated if multiple tags, ratings or comments are stored, leading to a risk of inconsistency.

Edit:

Since you asked for recommendations in your comment, I suggest:

1. Use Chen's ERD notation, or at least a variation that uses shapes to represent relationships and supports ternary and higher relationships. An even better approach would be Object-Role Modeling.
2. Use only Username as the primary key for your 4 user tables.

3. Split Uploaded file into the following tables:

   • Uploaded file (File_URL PK, File_title, File_description, Username FK)
   • File_tags (File_URL PK/FK, Tag PK)
   • File_ratings (File_URL PK/FK, Username PK/FK, Rating)
   • File_comments (Comment_ID PK, File_URL FK, Username FK, Comment, Created_at, Reply_to_comment_ID FK)

Note that this is just a minimal set of changes to address the issues I raised, meant for educational purposes, not necessarily a proper solution to your requirements.