There are three different levels of modeling, like below. The most helpful one is obviously the Physical one, but when the database is huge, a couple of hundreds of tables is just so normal these day, the more abstracted forms will be more preferable, as the number of tables grow.

Machine generated diagrams are the perfect solution for such problem, and hope erdiagram can support different levels of diagrams someday.

Thanks

Conceptual

• the most abstract form of data model
• simplified, helpful for communicating ideas to a wide range of stakeholders

Logical

• contains a bit more details than the almost-empty conceptual one, which will include all key structure data elements for the relationships (for table joining; and no more further details than that, unlike the borrowed image above)

Physical

• this is only a borrowed image; The key is to contain all fields, which has already been implemented. IF the PK/FK can be marked out, then that'd be super.

Currently, we are hiding the methods and members in conceptual diagrams (as both are empty). However, methods are not being hidden in logical diagrams, even when the diagram doesn't include any method.

We should ensure that the hide methods directive is used in both cases.

There are several scenarios where generated code (SQL, TS or Java) can produce several tables, columns or classes with the same name.

For example, if you define the following ER model:

User
    id int

Users

Role
    userId int

UsersRole

Role -> User
User *<->* Role

The following SQL code is produced for SQLite:

PRAGMA foreign_keys = OFF;

CREATE TABLE "users" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "id" INTEGER NOT NULL
);

CREATE TABLE "users" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "roles" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "user_id" INTEGER NOT NULL,
    "user_id" INTEGER NOT NULL,
    CONSTRAINT "roles_user_id_unique" UNIQUE ("user_id"),
    CONSTRAINT "roles_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id")
);

CREATE TABLE "users_roles" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "users_roles" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "user_id" INTEGER NOT NULL,
    "role_id" INTEGER NOT NULL,
    CONSTRAINT "users_roles_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "users" ("id"),
    CONSTRAINT "users_roles_role_id_fk" FOREIGN KEY ("role_id") REFERENCES "roles" ("id")
);

PRAGMA foreign_keys = ON;

The users and users_roles tables are duplicated, and the same happends to the roles.user_id column. We must implement one of the following solutions:

• Throw an error when this happen.
• Add an incremental suffix to the generated tables and columns
  • For example, if the Role entity has an explicit user_id column, the column produced by the Role -> User relationship must be named user_id2 or something similar.
  • The same applies to the corresponding table of the Users entity. As users table already exists (because the User entity is declared before Users), then the table must be named users2.
  • Also, we could return a result with warnings in those cases, as those scenarios are likely to be errors.

Export

Allow to add export keyword to the generated types. Enabled by default.

Class syntax

Instead of defining interfaces for the entities, generate classes like this:

class User {
    id?: number;
    firstName: string;
    lastName: string;
    age: number;
}

TypeORM

Along with class syntax, allow to generate TypeORM annotations, just like we did for Java and JPA.

Just putting it here as a long term goal --

For erdiagram to be truly helpful, then allowing people to start from existing things will be much appreciated than letting them starting from scratch. I.e.,

A translation tool to translate existing database's DDL to erdiagram's definition text would be super useful for 95+% of cases.

For sure it's not an easy task.