2

I have worked with PostgreSQL, primarily using the Django ORM (started with MySQL and migrated to PostgreSQL) and using pgAdmin for maintenance tasks.

I am now working on an open source project developing an alternative Q&A engine, ( Codidact.org if anyone is interested). We will be using PostgreSQL but are still working out a lot of details and no ORM has been selected yet.

Can anyone recommend a way to iteratively develop a database structure - tables, fields, FK relationships, etc. - in a way that would make it easy to produce both:

  • A development (detailed) and/or management (higher level) diagram of the functional components of the database.
  • CREATE TABLE commands compatible with PostgreSQL.

The goal is to have a schema definition that effectively generates both human-useful (diagram - i.e., not just "read the SQL code") and machine-readable code.

  • 1
  • @a_horse_with_no_name I have to go through the list myself, but if you have any real-world experience with any of the listed products then please post an answer with the link and the specifics. – manassehkatz-Moving 2 Codidact Nov 25 '19 at 15:18
  • 1
    I use the diagram in Navicat for Postgres. It is.....fine. Download trials of several tools and check them out, maybe you'll like one. – Morris de Oryx Nov 26 '19 at 2:30
  • 3
    You could also use the (excellent) MySQL Workbench - generate scripts from that and then run a few sed and awk scripts on them to convert them to PostgreSQL? There's also the (again excellent) Data Modeller from Oracle - you might be able to take the same approach? You will have to do some "legwork" - but depending on the size of your project, it might be a worthwhile approach. This might be of interest? p.s. welcome to the forum! 👍 – Vérace Nov 26 '19 at 7:00
  • 1
    In case anyone arrives here from Google, we ended up using pgModeler in Codidact for the time being. – Marc.2377 Jan 22 at 4:50