Schema Diagram Notation

Databases
1

I am thinking the answer to this question is “No”, but I figured I’d ask any ways.
Given only the contents of a databases’ Metadata (ie. SQLite_Master for example)… it is possible to create a ERD…this I know
but for that ERD… is there any way to determine what relations are 1->1, 1->M, M->M etc… the reason would be that the lines connecting the diagram nodes would have different end point symbols. (Crow Foot Notation)

I’m guessing that a person familiar with the database would have to “guess” and select an apporpriate connection type, on a instance by instance basis?

2

For sqlite I dont think you can do it 100% right
At best you have the foreign key definition (if someone has decided to include those) or references
http://sqlite.org/foreignkeys.html

And none of those detail cardinality of the releationship

3

I realize that the FK defines the connection… a subject that I have learned quite alot about in regards to SQLite in the past few days.
I was hoping there was something in the FK information that I might have missed that indicated the cardinality

4

Nothing that I’m aware of in the vast majority of databases indicates cardinality - just the foreign key

5

A long time ago there WAS a db erd editor done in RB / RS /Xojo called Xerd that could spit out sql for OpenBase, Postgresql and … I forget if it did mysql or not
http://www.monkeybreadsoftware.eu/listarchive-realbasic-nug/2004-02-12-2.shtml
http://joshuahawcroft.com/software/xerd/

I dont recall if you could pick the diagram style (chen ,martin , bachman) or not

6

something like this ? :
I extract all you can see from the foreign keys of the sqlite database
(and also postgres and mysql recently)

7

Jean-Yves:

I love that screen shot (contents). Onmy questionable to my eye is the orange color (not so readable); I would use one more darker color.

Years ago, I’ve drawn something similar (less nice) with the MenuBar (“complete” with keywork short cuts)…

8

I can do that ! :wink:

9

[quote=337582:@Jean-Yves Pochez]something like this ? :
I extract all you can see from the foreign keys of the sqlite database
(and also postgres and mysql recently)
[/quote]
Yes and no
Tadpole can draw that exact plcture… What I am asking is there a way to tell from the database schema what type of notation to use for each of the connecting lines…
Technically your image isn’t using any of these :slight_smile:

10

foreign keys only deal with one-to-many. (at least with sqlite)
many-to-many is in fact always made with an intermediate table, so is exploded into 2 one-to-many relations.

11

[quote=337594:@Jean-Yves Pochez]foreign keys only deal with one-to-many. (at least with sqlite)
many-to-many is in fact always made with an intermediate table, so is exploded into 2 one-to-many relations.[/quote]
I don’t think that is true

FK -> PK is many to 1
PK(fk) -> PK(fk) is 1 to 1
Pk(fk) -> FK is 1 to many
FK -> FK is many to many

12

I think Jean-Yves is right
https://www.visual-paradigm.com/support/documents/vpuserguide/3563/3564/85378_conceptual,l.html
http://www.1keydata.com/datawarehousing/data-modeling-levels.html
etc
jjc_Mtl

13

we can agree to disagree
If a FK references a PK… it has to be many to 1
now true, on the FK side it might actually be 1, 0 or many and you can’t “guess” that , but the “TO 1” part is true, since PK must be unique (there can be only one)

If the FK on both sides is also a PK (which is entirely legal), then it is in fact a “1 to 1” period

There are many other commercial Schema Diagrammers that “guess” in this EXACT same manner

14

What is the idea to connect a FK elsewhere than to the PK?
jjc

15

nothing says that an FK must reference a PK
nothing says that an FK must BE a PK

Create Table t1 ( id integer,  data text);

Create Table t2 ( id integer, data text);

Create Table t3 ( column1 text,
column2 text,
column3 text,
foreign Key ( column2) references t1 ( id)
     On delete no action
     On Update no action 
foreign Key ( column3) references t2 ( id)
     On delete no action
     On Update no action)

That is perfectly “Legal” data structure… it may not “make sense”… but it is “legal”
the 2 FK in T3 reference NON key values in T1 and T2

16

OK, the physical model is legal but what about the logical one?

17

At least PostgreSQL does enforce a Foreign Key to reference a column that is unique:

[code]# create table a(i int, u int unique);
CREATE TABLE

create table b(i int references a(i));

ERROR: there is no unique constraint matching given keys for referenced table “a”

create table b(i int references a(u));

CREATE TABLE
[/code]

18

I said “it might not make sense”… and my task is to show what the “physical” is… it is the responsibiltiy of whoever designed the database to be concerned about the logical.

I am going to display what “IS”… .not what “should be”

19

[quote=338979:@Dave S]nothing says that an FK must reference a PK
nothing says that an FK must BE a PK[/quote]
from the SQLite docs on the topic it seems, the example above would not be allowed by this DBMS either.

20

Not sure what you are trying to indicate?
Are you saying the SQLite would not allow the example I posted above? If so, I must strongly disagree… as I used SQLite to create it to begin with…

I am not saying what SHOULD be done, I am indicatating what CAN BE done.
My application needs to analyze an SQLite database, and inform the user of what IS there… again not to tell them what SHOULD or SHOULD NOT be.

The examples in those Documents are what you SHOULD do… .but SQLite imposes no constraints on what you might do

This discussion is polar opposites…
You folks (and rightfully so), and arguing that a developer should not do these things…
I am arguing that regardless, they CAN do them, and if they DO, then I will document what the database IS

So, I think we have reached an impasse,