That's not to say that the tool is useless or that diagrams of this sort are unhelpful. I'll admit I'm being pedantic and others will probably disagree.
Isn't the fact that they are _mostly_ interchangeable the foundational principle of hundreds of ORMs? Of course the DDL doesn't say much about the entity's lifecycle, but if the bar is set at representing its relationships, fields and cardinality as a graph, it seems sufficient?
I guess nowadays few people care about this difference.
so the db already exist, but they have no er, and maybe even little docs
so it act more like an exploratory tool, ideally, it should allow you to create views and add notes, so you dont have to look at the full er at once, especially if the number of table is huge, and if many of those tables are missing foreign keys
Many to many will lead to an extra table (which can have additional properties, requiring this table to be modelled as an entity), one to many leads to the inclusion of a foreign key to another unique key (referring all columns of that key, _id is an implementation decision, compound primary keys are possible). One to one can be implemented in multiple ways, like one to many with a uniqueness constraint by the referring table or even by merging entities to a single table.
The raw SQL can be revealing but when entities have merged into one table it’s harder to tell what is what, unless a certain set of columns appears over different tables.
Another is that in ER diagrams, relationships themselves can have attributes. Personally I think it tends to make more sense to convert relationships to entities in this case most of the time, but it can be useful.
Finally, relationships in ER diagrams can be N-ary and connect more than two entities while foreign keys in SQL always reference one other table. Of course you can have multiple foreign keys on a table to represent this, but not without some loss of semantics.
An example would be really helpful.
one man’s simplicity is another man’s headache