Displaying relations with or without crow's foot

[michi-zuri]

Dear Loïck and Samir

I like how you use the crow foot notation for one-to-many relations.

For one-to-one relations it doesn't look good, however. Here's an example:

As you can see, the chick hatches from one egg and since the fk chick.id (to egg.id) is itself a primary key, there cannot be more than one chick from one egg. To adapt this model to the real world (with the possibility of having twin chicks from one egg) the chick would need a pk separate from the egg.id. I would propose that the crow foot is not shown if the fk is itself the sole (not composite) pk, instead showing a vertical line (for one) like so, following the gist of crow's foot notation:

I would also like to be able to attach comments to relations like so, which causes an error right now:

fk egg.mother -> mother.id | one mother chicken may lay many eggs

Also note: in collapsed view I am missing the crow's feet and the curved lines!

see also https://azimutt.app/bba7e0f1-71cc-4f2b-be3d-ac470178d186/40bbceda-fab9-451e-83fa-b617226d4090?layout=relations&token=8d0c79bc-c7ee-4ca0-9b5e-3713041f27db

[loicknuchel]

Hi @michi-zuri

You're right, we should show a one-to-one relation if the foreign key target a unique column (alone in pk or unique constraint).
Would be a good improvement 😄

On the relation comments, I though about it and could easily add it in AML but what is more tricky to me is to integrate it in the UI. For now you can't select a relation and they are too thin to handle hover and click events.
Do you have a suggestion to show relation comment/notes in the UI?

For the collapsed view, I hesitated between the actual implementation and attaching them to the sides. Will give it a try and see how it goes.

Thanks a lot for your feedback, you're helping making Azimutt better 👍

[michi-zuri]

hey @loicknuchel, yeah that seems right. Don't forget to think of composite unique constraints though, they are a thing in dbms systems. Not sure if AML supports those.

You're very welcome for the feedback, thank you for keeping this software growing, it's a work of art!

[loicknuchel]

For now composite unique constraints are well supported in Azimutt (unless you tell me otherwise ^^), that's why we need to check the foreign key reference a single column constraint to be a one-one.
But the composite foreign keys constraints are not supported yet. The biggest obstacle is also the UI. I'm not sure how to represent them ^^

In AML, you can have a composite unique like this:

my_table
  item_kind varchar unique=id
  item_id uuid unique=id
  name varchar

If the unique key has the same name (after the =) they will be the same constraint. This is the same with primary key, if you put it on multiple columns they all will be part of the primary key (no need for a name as there is only one primary key ^^)
Maybe not as clear as it should be in https://github.com/azimuttapp/azimutt/blob/main/docs/aml/README.md#column-modifiers ^^

[michi-zuri]

Ah cool, thank you for pointing me to the documentation, I find it perfectly clear.

I just realized now that I actually need a composite unique constraint in a join table where an additional id pk is added to the defining fks. By adding the extra id column one can avoid the need for composite foreign keys if one wants to reference the relation object in yet another table). Probably easier to follow with an example:

table1
  id Int pk

table2
  id Int pk

jointable
  id Int pk
  table1_id Int unique=join fk table1.id
  table2_id Int unique=join fk table2.id

table3
  id Int pk
  jointable_id Int fk jointable.id

I noticed here that the current visibility of the unique constraint is quite limited if a fk constraint exists as well on the same column like in the example above (no difference between fk+unique and only fk). I personally would give precedence to the unique constraint when deciding which icon to show, because the fk is obvious from the arrows pointing out on the side. Would it be possible to add a little number footnote for composite uniqueness? The first unique group would have a 1 as footnote, the second unique group a 2, no group would have no footnote.

As for your question, how to visualize composite fk constraints, tricky yeah. Maybe by obviously merging the lines close to the origin and target in a merge point like this?

Oh and Here's another idea for the schema analyzer, should probably open a separate issue for this:

• give a warning for foreign key constraints that point to something that has no uniqueness constraint.

[michi-zuri]

Do you have a suggestion to show relation comment/notes in the UI?

One way could be: making single columns selectable and only showing the comment and note buttons for the fk relations parting from that column.

[loicknuchel]

Lot of good ideas here, and lot of work ahead ^^
Would you be interested to contribute? Some should be quite easy like fk icon priority or one crow's foot.

Just to keep this clear, here's the list of proposed ideas:

• use a one crow's foot notation when foreign key references a unique column (pk or unique constraint with only this column)
• add comments and notes to relations
• keep curved relations with collapsed tables (left & right anchors)
• composite foreign keys
• make foreign key icon the less priority as it's already very visible with relations
• for composite unique constraint, show its number as foot note to make it clearer
• analyzer: foreign key should reference columns with unique constraint

[michi-zuri]

Hey @loicknuchel, yeah I'd be thrilled to contribute code if I could. Unfortunately I haven't come much further than hello world in Elm so far, but this could be the opportunity to dive in. Which one do you consider the easiest for beginners? I have dabbled in vanilla php and vanilla js... I may try to start with the fk icon priority.

[loicknuchel]

That's a big project to start but if you want to learn Elm that's a good opportunity 😉

We try to keep the project easy to work with but as we don't have many contributors for now, it may have some rough edges, feel free to report any difficulty you may have (for the setup as well as understanding the code) so we can figure out how to improve it 💪

Indeed, I think the easiest one is fk icon priority (Table.elm#L331, should be easy but a good first step in the project 😉)

Other easy ones should be:

• foot note for composite unique constraint (Table.elm#L331)
• analyzer foreign key references unique column (SchemaAnalysis.elm#L87)
• one crow's foot notation (Relation.elm#L119)
• curved relations for collapsed tables (Relation.elm#L36)

If you have any trouble to setup the project (it needs the Elixir backend as well as the Elm stack) feel free to ask. I will soon add a CONTRIBUTING.md to make the installation steps easy but for now it's still a bit rough ^^