Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

http://i.stack.imgur.com/XzGWz.png

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
share|improve this question
    
Identifying and non-identifying relationships are really logical modelling concepts that come from the chen notations (the one with the diamonds for relationships). An identifying relationship is just one where the PK of the parent forms a part of the PK of the child and the child has a FK to the parent. –  ConcernedOfTunbridgeWells Mar 15 '12 at 16:47
    
I guess I18n is not part of the requirements. –  DanMan May 24 at 10:09

2 Answers 2

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.