Is my EER Diagram correct? Why use relationships?

Question

This is the first time I've made an Entity Relationship Diagram (ERD) for MySQL.

Prior to this, I would never create relationships between tables, I would simply create the tables in MySQLAdmin and do the linking in my PHP/MySQL code.

Is there a reason why I should create these relationships?

I have read a lot of info on database design and relationships.

I want to be sure the ERD I've created is correct; did I make mistakes? Or are there better ways to do this?

What my database should do:

1. I have a list of users (users) that can create tickets (issue_tickets).
2. They will give a description of the problem, maybe add some files (issue_files).
3. Then other users (issue_watchers) will be attached to solve the problem.
4. All the users (users) can comment on a ticket (issue_tickets).
5. A ticket (issue_tickets) only has one status (issue_status) at a time.
6. A ticket (issue_tickets) only has one category (issue_category).
7. A ticket (issue_tickets) can have many comments (issue_comments) and many watchers (issue_watchers)
8. A ticket (issue_tickets) only has one user (users) who created the ticket.
9. A ticket (issue_tickets) only applies to one equipment (equipment).
10. A user can create many tickets
11. A equipment can have many tickets

Answer 1

It is a very poor practice to expect to maintain the PK/FK relationships from the application. In any db that is nontrivial, data has close to a 100% chance of being changed from other sources including ad hoc queries, data imports, etc. It is irresponsible to think the data in the database is protected because the application has protections. Sure you think that you will force all changes through a webservice or some such, but the reality is that no one is going to add a million new customer records, from that company you just bought, one record at a time through a service. Your database has to be designed to account for the fact that people will change it directly at the source or through other applications (some of which may not be able to use your web service). Further, the application interface is usually more likely to be thrown away or redesigned than the data and when that happens, you may lose all or some of the data integrity rules. In designing databases, you have to think about how the data needs to be protected over time not about what is easiest and most convenient for the programmer or the intial application. A data professional sets up PK/FK relationships in the database because that is where the data lives and it is the best place to set it up to protect the data quality.

I see data from a lot of different companies in my current position and I see all too many that didn't set up their PK/FK relationships in the database because they have data integrity problems that a correctly designed database would not have.

Answer 2

Your EER diagram meets all the business rules you have documented a few additions to your design:

a) You need to track actions on a ticket i.e, when it was opened, when a watcher was added, when status changed etc for auditing purposes

b) Column naming - you use users_id throught, but each foreign key should be named according to the purpose for example: - issue_tickets - created_by or submitted_by - issue_comments - submitted_by

c) table naming - this is just my style, but I recommend naming the tables using singular, issue_ticket etc