I am working on a new django project which will use a MySQL database. Obviously there are several tables and attributes items in those tables have. I realize I could just document those attributes in the code itself, but more than one codebase may be accessing this database. I would rather have a more comprehensive solution to document relationships, expected CASEing of the text, allowed characters, etc.

I know UML exists, but it seems there are 1,000+ tools which do UML modeling, not all of which will gracefully do an SQL database.

Examples of things I want to document:

  • For a “user profile” there are various attributes: username (primary key), friendly name, etc
  • For a “task” - id (primary key), name (letters numbers and spaces only, max 56 characters), owner (a single username (foreign key(), assignees (zero or more usernames (list of foreign keys)), etc

Here’s what I need:

  • GUI for building flowchart/model/whatever you call it showing each table and each attribute in each table, with ability to add notes to table or attribute. Attributes must have ability to be relational just like in a database.
  • FOSS only, must run on Linux. No “free” web-based garbage that will end up behind a paywall 5 years from now ie draw.io
  • Must store source files for this model in a text/xml/json/something file which can easily be put into our git repo
  • Must not be so tightly coupled to MySQL that is requires a database connection to work or couldn’t be used if we switch to a non-MySQL backent. If it has templates for and knowledge about MySQL databases that’s great but it shouldn’t require them to be useful.

What do you suggest for this?

  • @RegalPotooEnglish
    2•1 year ago

    Generally, I consider using your database schema as an API like this to be a bad architectural choice, especially if you are using an ORM like Django where you don’t have direct control over how the schema is structured.

    If it was me, I’d prefer to design an interface to allow the other applications access to the data they need, so as to maintain the abstraction between the logical structure of your models and the messy reality of SQL. Build an actual REST API, define an Open API schema to document it.