0

How does database design or ERD diagram modeling works in user managament systems where exists users table and exists a lot of other tables like, orders, products, customers, notes, comments, etc. Each of those other tables can have created_by (pointing to users table) and edited_by (as well pointing to users table) attributes.

This makes me confused because at the end ERD diagram looks with a lot of associations to one and the same table (users). The diagram is not readable since there are that much associations to one place.

Or is this a database design problem? - I mean, maybe each table should not contain those two attributes but it should be handled in some other way?

Toms Bugna
  • 502
  • 2
  • 10
  • 30

2 Answers2

4

The short answer is that there's nothing wrong with this.

It's important to remember that different designs exist for different reasons. While I'd expect to see this information on a physical model (i.e. one showing the exact schema of your intended database), it's quite possible that you might choose to leave such an implementation detail off conceptual and even logical models for readability. Arguably things like system auditing are just an implementation detail - in higher level designs, that information isn't going to be useful.

The other thing to remember is that models don't always need to be one diagram. They often do get too big and messy to look at in a single diagram, and it can be a very reasonable decision to split them out by subject area - a diagram about orders, a diagram about customers, and so on. This can really help with readability.

Jo Douglass
  • 2,055
  • 1
  • 19
  • 30
1

In an ER model, there can be more than one relationship between two entities. You have outlined a case of this. This is driven by the subject matter itself, and is discovered during the analysis phase of the project. The lines in an ERD depict these discovered relationships. If this is complicated, it's either because the subject matter is complicated or because the analyst made a mistake.

In database design (at least relational design), relationships are implemented by foreign keys that reference a given row in a given table. Presumably, the design agrees with the analysis. Many ERDs are drawn up after the design is done, and reflect the relational model of the designer rather than the ER model of the analyst.

A lot of today's developers simply do analysis in their heads, and proceed directly to design. For simple cases, this saves time. For complex cases this can come back to bite you. Errors in analysis are not discovered until they are buried under layers of design.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58