virtual PK, virtual alternative unique keys, virtual relations; separate files to define them

[aisbergde]

Expected Behavior

it should be possible to define virtual PK, virtual alternative unique keys and virtual relations which will be used to generate the documentation.
PK and unique keys should be possible on views and tables.
Relations should be possible between

• view-view
• table-table
• table-view

it should be possible to define multi-column-relations

Current Behavior

only tables can have PK and physical FK-PK-relations can exist only between tables

Possible Solution

special parameters could be used
better: special files could be used where the PK and relations can be listed in a required format

Steps to Reproduce (for bugs)

Context

I use the AnalyticsCreator to build my DWH's. Each project has it's own repository. There is much more information in the repositories than can be used in the generated DWH. Because of technical limitations in the physical database (for example regarding relations and PK on views).

The main logic in my DWH's is often in views and relations between views, but not in tables. All relations are virtual relations there are no physical relations. But I want to document these virtual PK and virtual relations.

It would be possible to create special files containing information about PK and relations and SchemaSpy could use them and show in diagrams.

I could automatically generate the content of these additional files from my AnalyticsCreator repositories because these are also databases.

• Database type (e.g. MySQL): MS SQL Server

Your Environment

[npetzall]

https://schemaspy.readthedocs.io/en/latest/configuration/schemaMeta.html

For the table to table.

Need to check on the view thing. I'm even unsure if they are included in the diagram.

The view thing is a bit different. Need to look stuff up. I've never heard of any views having FK. But it might be possible. I would imagine that you might want to know the sources of a view.

[aisbergde]

Sorry, that I did not read the full documentation. This is what I need, and it should be possible for me tor create this as XML.

But the major requirement are the views:

Of course in real life there are no real FK on views. They are only virtual. But I need them to document.
Example:
I create different data marts for different user groups or applications. And by technical design of the AnalyticsCreator they are always views. But I need a way to document the virtual relations between these views.

I think if SchemaSpy is able to print relations between tables it should be also possible to print these virtual relations between views.
Think about this construct: each view has a related virtual "table". BTW, this is the way AnalyticsCreator works: Every view (= Transformation) has a related virtual Transformation-Table and all the table specific stuff is implemented the same way for real tables and transformation-tables, all they are tables. Only some stuff is not available on Transformation-tables, for example calculated columns.

I would imagine that you might want to know the sources of a view.

Of course I would like, but I wanted first see your reaction on my other feature request. Now you wrote this and I will create separate feature requests :-)

BTW: I found SchemaSpy when searching a way to use Markdown in descriptions to document databases. And the SchemaSpy example was and is exactly what I am looking for.

And now I hope some other required stuff will be implemented. And your very quick responses on issues are very promising!

Since 2005 this is my favorite tool: SqlSpec from http://elsasoft.org/, but the development has stopped a few years ago, and no Markdown support will be implemented, nothing more will be implemented. And if I can't ask anymore to improve SqlSpec then my dream is to extend SchemaSpy with some useful features of SqlSpec to be able to switch :-)