Detect the cardinality of unique foreign key relationships

[aaditmshah-commversion]

1. many to 1 is a non-nullable foreign key.
2. many to [0..1] is a nullable foreign key.
3. [0..1] to 1 is a unique non-nullable foreign key.
4. [0..1] to [0..1] is a unique nullable foreign key.

The cardinality of ref: > relationships should be inferred from the unique, null, and not null constraints.

Table posts {
  // A user may create many posts. A post has only one creator.
  created_by integer [ref: > users.id, not null] // many to 1

  // A user may update many posts. A post might not be updated.
  last_updated_by integer [ref: > users.id, null] // many to [0..1]
}

Table compliance_training_completions {
  // An employee might not have completed the compliance training.
  // A completion record is specific to one employee.
  created_by integer [ref: > employees.id, unique, not null] // [0..1] to 1
}

Table departments {
  // A department may or may not have a manager.
  // An employee can manage at most one department.
  manager_id integer [ref: > employees.id, unique, null] // [0..1] to [0..1]
}

Correct me if I'm wrong. Currently, there's no way to specify the [0..1] to 1 and [0..1] to [0..1] relationships in DBML.

[huydo862003]

Hi @aaditmshah-commversion,

In DBML:

• To specify [0..1] to 1 relationships, you can use the - type of ref, like this:

It will show up as a [0..1] to 1 as in the picture.

• The [0..1] to [0..1] relationship is not supported yet though.

Hope this answers your question.

[aaditmshah-commversion]

@hdnax It doesn't work when a.id is declared as a primary key.

Also, in your example the relation should be many to [0..1] because b.id is nullable and not unique. So, a row in B could be related to [0..1] rows in A. However, a row in A could be related to many rows in B.

I'm operating under the assumption that the columns that the foreign key references must be unique. However, here we're creating a reference to a non-unique column. That shouldn't be allowed. And, if it's allowed then the relation becomes many to many.

[huydo862003]

Hi @aaditmshah-commversion

• If you want a.id to be a primary key, then you can swap the endpoints of the ref like a.id - b.id instead of b.id - a.id. I admit that this is a bit awkward and there should be a better way.
• Regarding the uniqueness constraint, yeah, it's true that the target of an FK should be unique in virtually all major relational DBMS (with Mysql/InnoDB being an odd exception, although non-unique fk targets are unusual).
  • However, I want to clarify that DBML operates at a higher level than SQL. It's the dbml2sql exporter that tries to export valid SQL. An example of this is the fact that DBML supports the many-to-many relationship <>, which is translated into junction tables in SQL (so that the targets of FK can be unique). Similarly, - is meant to model 1/[0..1]-to-1 relationships in DBML.
  • On the other hand, I do agree that dbml2sql exporter should respect SQL's fk uniqueness requirements by implicitly adding a unique constraint to the referenced column in the output SQL when translating the DBML refs. For example, - should implicitly add uniques to both columns in the output SQL.

What do you think? If you have any suggestions on some small improvements, I can definitely work on a quick patch or something like that

[aaditmshah-commversion]

What do you think? If you have any suggestions on some small improvements, I can definitely work on a quick patch or something like that

@hdnax I've been thinking about this problem rigorously for the last few days. Here's what I discovered.

Referrer

The table with a foreign key constraint referencing a key in the referent table.

Referent

The table with a key that's referenced by a foreign key constraint in the referrer table.

Cardinality	Semantics
Many	Unrestricted
Some	At least one
Maybe	At most one
One	Exactly one

Referrer		Referent		Cardinality
Unique	Not Null	Unique	Not Null	Referrer	Referent
❌	❌	❌	❌	Many	Many
❌	❌	❌	✅	Many	Many
❌	❌	✅	❌	Many	Maybe
❌	❌	✅	✅	Many	Maybe
❌	✅	❌	❌	Many	Some
❌	✅	❌	✅	Many	Some
❌	✅	✅	❌	Many	One
❌	✅	✅	✅	Many	One
✅	❌	❌	❌	Maybe	Many
✅	❌	❌	✅	Maybe	Many
✅	❌	✅	❌	Maybe	Maybe
✅	❌	✅	✅	Maybe	Maybe
✅	✅	❌	❌	Maybe	Some
✅	✅	❌	✅	Maybe	Some
✅	✅	✅	❌	Maybe	One
✅	✅	✅	✅	Maybe	One

The first thing to note is that the nullability of the referent key affects neither the referrer cardinality nor the referent cardinality. If the key of a row in the referent table is null, then that row can never be referenced. But, that doesn't restrict the cardinality of non-null rows. Hence, we can simplify the above table.

Referrer		Referent	Cardinality
Unique	Not Null	Unique	Referrer	Referent
❌	❌	❌	Many	Many
❌	❌	✅	Many	Maybe
❌	✅	❌	Many	Some
❌	✅	✅	Many	One
✅	❌	❌	Maybe	Many
✅	❌	✅	Maybe	Maybe
✅	✅	❌	Maybe	Some
✅	✅	✅	Maybe	One

Next, we can decompose this table into two simpler tables.

Referrer	Cardinality
Unique	Referrer
❌	Many
✅	Maybe

Referrer	Referent	Cardinality
Not Null	Unique	Referent
❌	❌	Many
❌	✅	Maybe
✅	❌	Some
✅	✅	One

1. If the referrer key is unique, then every row in the referent table may be related to at most one row in the referrer table.
2. If the referrer key is not nullable, then every row in the referrer table must be related to some row in the referent table.
3. If the referent key is unique, then every row in the referrer table may be related to at most one row in the referent table.
4. If both points 2 and 3 are true, then every row in the referrer table must be related to exactly one row in the referent table.

Encoding these inference rules in DBML would make it a much better modeling language, akin to how type inference in programming languages makes them better. We can call it cardinality inference. Here's what I had in mind.

Note, cardinality inference is only applied to the < and > modifiers. The - and <> modifiers continue to behave as they currently do.

1. many to many

   Table a {
     id int
   }
   
   Table b {
     id int [ref: > a.id]
   }
   

2. many to maybe

   Table a {
     id int [unique]
   }
   
   Table b {
     id int [ref: > a.id]
   }
   

3. many to some

   Table a {
     id int
   }
   
   Table b {
     id int [ref: > a.id, not null]
   }
   

4. many to one

   Table a {
     id int [unique]
   }
   
   Table b {
     id int [ref: > a.id, not null]
   }
   

5. maybe to many

   Table a {
     id int
   }
   
   Table b {
     id int [ref: > a.id, unique]
   }
   

6. maybe to maybe

   Table a {
     id int [unique]
   }
   
   Table b {
     id int [ref: > a.id, unique]
   }
   

7. maybe to some

   Table a {
     id int
   }
   
   Table b {
     id int [ref: > a.id, unique, not null]
   }
   

8. maybe to one

   Table a {
     id int [unique]
   }
   
   Table b {
     id int [ref: > a.id, unique, not null]
   }
   

[aaditmshah-commversion]

I found a few related issues.

1. Add mandatory/optional relationship notation #63
2. Zero to Many #268
3. Extended support for relationship types #681

[huydo862003]

I really appreciate your thorough suggestions 😸

I think I will need to discuss these points with the team. I'll let you know when we have any updates.

[aaditmshah-commversion]

I want to clarify that DBML operates at a higher level than SQL.

@hdnax I've been contemplating this statement — DBML operates at a higher level than SQL. It makes sense. DBML is a database modeling language, even though the acronym stands for database markup language. And, as a modeling language it shouldn't be constrained by the real world. The goal is to enable you to model your database in an ideal world.

That's why I propose adding a new constraint, covers, to DBML. The covers constraint is the dual of the unique constraint. The unique constraint enforces that a column is injective. The covers constraint enforces that a column is surjective. Currently, no database in the world, as far as I know, has a constraint for surjectivity. However, DBML shouldn't be constrained by real world implementations of databases. There are valid use cases to mark a column as surjective.

If we add the covers constraint to DBML then it completes our table.

Referrer		Cardinality
Covers	Unique	Referrer
❌	❌	Many
❌	✅	Maybe
✅	❌	Some
✅	✅	One

Referrer	Referent	Cardinality
Not Null	Unique	Referent
❌	❌	Many
❌	✅	Maybe
✅	❌	Some
✅	✅	One

Now, we can create any combination of cardinalities for a relation. In particular, the - modifier is completely superseded. Note, that <> is still required for two reasons. First, it allows arbitrary rows to be related. Second, it's used to automatically create junction tables when exporting DBML to SQL.

Referrer			Referent	Cardinality
Covers	Unique	Not Null	Unique	Referrer	Referent
❌	❌	❌	❌	Many	Many
❌	❌	❌	✅	Many	Maybe
❌	❌	✅	❌	Many	Some
❌	❌	✅	✅	Many	One
❌	✅	❌	❌	Maybe	Many
❌	✅	❌	✅	Maybe	Maybe
❌	✅	✅	❌	Maybe	Some
❌	✅	✅	✅	Maybe	One
✅	❌	❌	❌	Some	Many
✅	❌	❌	✅	Some	Maybe
✅	❌	✅	❌	Some	Some
✅	❌	✅	✅	Some	One
✅	✅	❌	❌	One	Many
✅	✅	❌	✅	One	Maybe
✅	✅	✅	❌	One	Some
✅	✅	✅	✅	One	One

1. some to many

   Table a {
     id int
   }
   
   Table b {
     id int [ref: > a.id, covers]
   }
   

2. some to maybe

   Table a {
     id int [unique]
   }
   
   Table b {
     id int [ref: > a.id, covers]
   }
   

3. some to some

   Table a {
     id int
   }
   
   Table b {
     id int [ref: > a.id, covers, not null]
   }
   

4. some to one

   Table a {
     id int [unique]
   }
   
   Table b {
     id int [ref: > a.id, covers, not null]
   }
   

5. one to many

   Table a {
     id int
   }
   
   Table b {
     id int [ref: > a.id, covers, unique]
   }
   

6. one to maybe

   Table a {
     id int [unique]
   }
   
   Table b {
     id int [ref: > a.id, covers, unique]
   }
   

7. one to some

   Table a {
     id int
   }
   
   Table b {
     id int [ref: > a.id, covers, unique, not null]
   }
   

8. one to one

   Table a {
     id int [unique]
   }
   
   Table b {
     id int [ref: > a.id, covers, unique, not null]
   }
   

[huydo862003]

Hi @aaditmshah-commversion, again, thank you for the detailed suggestions. Since this feature can involve some large changes & proper alignment with our mental model of DBML, we will need to consider it more carefully, though.

[aaditmshah-commversion]

@hdnax Absolutely. Take all the time you need. I know that it's a big request.

P.S. I love the work you folks did with DBML. It's an awesome language. It would be an honor to contribute to this project in whatever way I can.

[Kiiv]

Hi,

I second the suggestion of @aaditmshah-commversion 👍
I'm not able to modelize a "[0..1] to one" relationship in the right direction at the moment.

[aaditmshah-commversion]

Hey @hdnax, it's been three weeks since we last communicated. Hopefully, the team and you have got some time to review my suggestions. In addition, I see that you're interested in type theory and category theory. I'm interested in those topics too. I'd love to connect with you. I believe there's a lot we can learn from each other. Let me know if you're available for a quick conversation. I'd love to introduce myself and get to know you better.

[huydo862003]

Hi @aaditmshah-commversion, we're on Lunar new year holiday so we're taking some time off. But be assured that we have already discussed your suggestions. In particular, we have some vision about clearly segregating the more "conceptual" modeling feature of DBML (such as many-to-many relationship) from the more "physical" modeling features of DBML (such as indexes). Your suggestion clearly lies in the "conceptual" modeling feature. Still, it takes time to do some planning though and our roadmap is a bit tight: link. I will definitely notify you should we have some forward movement. 😁

By the way, I'm always available for a chat. You can connect with me via discord. Hope to learn a lot from you too 👍!

[TeaNguyen]

Thanks a lot for the clear breakdown of the four cases (nullable vs non-nullable, unique vs non-unique). Your examples make the gap very easy to see.

Current state: DBML mixes abstraction levels

Today, DBML effectively has two layers mixed into the “relationship” concept:

1. Logical / conceptual notation via the 4 relationship operators: <, >, -, <>
   These are primarily a notation to help users describe relationships at the ER/conceptual level, and the tooling then generates the corresponding physical SQL constraints (FKs, indexes, etc.) from the schema.

2. Physical / database-aligned optionality via column nullability (null / not null)
   When we want to express “zero” participation (optional relationship), DBML currently relies on the physical FK column being nullable.

This creates a mismatch:

• It forces users to describe a conceptual modeling fact (“optional relationship”) using a physical detail (nullable constraint).
• And more importantly: nullable in a DB doesn’t always fully enforce optionality intent. Optionality can be conditional, can be enforced via app logic/check constraints/triggers, or can be expressed through modeling patterns that don’t map 1:1 to nullability. So it’s both harder to read and not always accurate as “relationship semantics”.

Because of this historical approach, DBML currently lacks a clean, fully-notational way to express optional relationships [0..1] -> [0..1] as relationship notation, independent of physical column constraints.

Proposal: make relationships fully conceptual/logical with a new ? marker

To close the abstraction gap and bring DBML relationships back to the mission of being “simple, consistent and highly-readable”, we’d like to make optionality part of the relationship notation itself—i.e., keep Ref entirely at the conceptual/logical level.

Concretely, we’re considering adding a ? marker to represent “optional (0)” on an endpoint, so users can express min cardinality directly in the relationship operator.

Examples of proposed operators:

• -? for 1..0 (mandatory on left, optional on right)
• ?- for 0..1 (optional on left, mandatory on right)
• >? for ∗..0 (many on left, optional on right)
• ?< for 0..∗ (optional on left, many on right)

(Exact naming/direction semantics can be refined, but the goal is: optionality is expressed in the relationship symbol, not by relying on nullable constraints.)

What DBML could look like with the proposed solution

1. some to many ([1..*] -> [0..*])

Table a {
  id int
}

Table b {
  id int [ref: <>? a.id]
}

2. some to maybe ([1..*] -> [0..1])

Table a {
  id int
}

Table b {
  id int [ref: >? a.id]
}

3. some to some ([1..*] -> [1..*])

Table a {
  id int
}

Table b {
  id int [ref: <> a.id]
}

4. some to one ([1..*] -> [1..1])

Table a {
  id int
}

Table b {
  id int [ref: > a.id]
}

5. one to many ([1..1] -> [0..*])

Table a {
  id int
}

Table b {
  id int [ref: <? a.id]
}

6. one to maybe ([1..1] -> [0..1])

Table a {
  id int
}

Table b {
  id int [ref: -? a.id]
}

7. one to some ([1..1] -> [1..*])

Table a {
  id int
}

Table b {
  id int [ref: < a.id]
}

8. one to one ([1..1] -> [1..1])

Table a {
  id int
}

Table b {
  id int [ref: - a.id]
}

9. maybe to maybe (([0..1] -> [0..1]))

Table a {
  id int
}

Table b {
  id int [ref: ?-? a.id]
}

---

Also, thank you for suggesting the covers concept. It does solve the problem from a modeling expressiveness standpoint.

That said, we worry it may:

• add significant conceptual surface area for users (more rules to learn),
• and still keep us tied to the database-aligned/nullable approach for optionality that caused the current inconsistency.

So we’re leaning toward the ? notation approach above as a simpler, more consistent fix, but we’ll definitely keep the covers concept in mind during design.

---

If this direction sounds aligned with your original goal (inferring cardinality without overloading physical constraints), we’d love to know your feedback.

Thanks again—this issue is a great articulation of the mismatch and why it matters.

[huydo862003]

So far I think this introduces a conceptually clean way to express a complete set of cardinality.

1. First point:

And more importantly: nullable in a DB doesn’t always fully enforce optionality intent

Just want to make it clear - So in this terminology, "nullable" is a physical term, while "optionality" is a conceptual term. This proposal aims to find a way for DBML to express optionality in references without enforcing nullability (but can map to nullability if it fits the target system)

2. Second point:

Examples of proposed operators:
-? for 1..0 (mandatory on left, optional on right)
?- for 0..1 (optional on left, mandatory on right)
>? for ∗..0 (many on left, optional on right)
?< for 0..∗ (optional on left, many on right)

• If we disregard that real-world DBMS mandates referent columns to be unique, according to @aaditmshah-commversion's table, there are 16 cases (one, some, maybe, many on each side, so 4^2).
• Considering our approach: -, >, <>, < (4 ops) each with ? on none, left side, right side, both sides (4 cases), then we will have 4^2 = 16 cases.

-> This proposal provides complete coverage

3. Third point:

That said, we worry it may:

• add significant conceptual surface area for users (more rules to learn),
• and still keep us tied to the database-aligned/nullable approach for optionality that caused the current inconsistency.

I agree, especially the 2nd point. Essentially, the covers approach requires us to mandate a column to be nullable/non-nullable (a physical concern) to express a certain cardinality (a conceptual concern).

4. A final consideration, we have some feature requests to model [m...n] to [m...n]. 👀