[lug] Q. on SQL Foreign Keys (PostgreSQL)

[Bear Giles]

Neo4j is noSQL.

What language(s) are you using?  I know how to collect the table metadata
(columns, indexes, foreign keys, etc.) in Java and it would be easy to spit
it back out in something like xml or json.

However what do you plan do to with it. I don't remember if drawing a
planar graph is NP-complete but with a thousand tables and multi-column
referential integrity keys any physical depiction is going to be difficult.
At a minimum you'll probably want to find some way to partition the tables
into somewhat cohesive subunits.

Note: if this is an old database and you can do this work on a copy (so you
don't break everything) you can move the cohesive subunits into their own
schemas. The cross-schema references just use a fully-qualified name. Each
schema will be a lot easier to understand and maintain since it will be
much more focused (and maybe even akin to a microservice architecture),
e.g., for a pseudo-Amazon you would have separate schemas for customer
accounts, payment information, orders (what was ordered), shipments (when
was it shipped? to where?), book information, book reviews, etc.

At the end of this you can create views so that all of the tables and views
remain in their new schemas but still appear to be in a single schema
(probably 'public') that the application already expects.

Oh yeah - back to the plotting. There's a plotting library that takes a
graph description and produces a dynamic image of it. You can tug on
individual nodes to reorganize it. I'm drawing a complete blank on the name
of it though - it's not java specific. It's used on a number of websites
though, and IIRC you can customize it so that the graph nodes could be ERD
elements although the results would probably be utterly unreadable.

On Wed, Aug 23, 2017 at 2:40 PM, <stimits at comcast.net> wrote:

> It sounds like Neo4j is an actual database, rather than a tool to map
> other people's database (though it looks like something I would put on a
> PostgreSQL wish list).
>
> I specifically have data from other people's applications for which the
> applications evolved over time and the original interface to install the
> data was lost. The same data is used, but only direct SQL edit is now
> possible on part of that data. I need to be able to map out some load
> orders from some XML files in order to correctly edit that sample data for
> real world use, and secondarily create a working interface after figuring
> out what is missing. The database in use is PostgreSQL, so if Neo4j can map
> this, I'm all for it. It seems though that I would have to dump
> PostgreSQL...which isn't really an alternative. Plus this is all free work
> and free software running on Linux, no money is available for purchasing
> commercial licensing or third party support.
>
> ----- Original Message -----
> From: Alan Robertson <alanr at unix.sh>
> To: lug at lug.boulder.co.us
> Sent: Wed, 23 Aug 2017 20:24:51 -0000 (UTC)
> Subject: Re: [lug] Q. on SQL Foreign Keys (PostgreSQL)
> Maybe use Neo4j?  ;-)
> /me ducks...
> --
>   Alan Robertson
>   alanr at unix.sh
> On Wed, Aug 23, 2017, at 01:25 PM, stimits at comcast.net wrote:
>
> Hi,
>
> I'm wondering if there is someone I might be able to ask a question
> off-list about SQL foreign keys (I'm using PostgreSQL, but this should
> apply to most SQL servers). Basically I'm trying to build an application
> which will read metadata tables and draw a graphical chart of complex
> relations (perhaps 1000 tables and almost all interrelated) and having a
> problem with foreign key descriptions. I've been trying to figure this out
> for quite some time, but can't wrap my brain around it. I want to know if
> my idea of how multi-column foreign keys work is correct, or if I need to
> approach this in a different way. Thanks!
> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> Join us on IRC: irc.hackingsociety.org port=6667 channel=#hackingsociety
>
>
> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> Join us on IRC: irc.hackingsociety.org port=6667 channel=#hackingsociety
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lug.boulder.co.us/pipermail/lug/attachments/20170823/2f8629e2/attachment.html>