Stateful constraints in TTM
If I understand correctly, all TTM constraints are meant to be reducible to a single static constraint on the entire database. I have, however, come up with a use case that seems plausible but cannot be so reduced.
Consider the classic employee-department database. The business rules are:
- Every employee must belong to exactly one department
- When a department is created, it can exist only on paper, with no employees yet
- A department which loses its last employee ceases to exist.
Rule 1 is no problem: the emp relation has an attribute dept_id and the constraint is simply that every emp.dept_id is equal to some dept.dept_id.
Rule 2 means that it must be possible to insert a department without any employees. Indeed, by Rule 1 nothing else is possible, since an employee without a department can't even exist.
Rule 3 means that a department tuple cannot be deleted if there are any joinable employee tuples.
So Rules 2 and 3 taken jointly constitute a constraint on the history of the dbvar rather than on its current state. Initially, a department without employees is valid, but once it has, or has had, employees, it can no longer exist without them. That can't be reduced to a simple mathematical constraint on the whole dbvar. On the other hand, it's easy to model as constraints on insert and delete actions.
How should such a thing be handled in TTM?
Quote from johnwcowan on August 1, 2019, 4:21 pmIf I understand correctly, all TTM constraints are meant to be reducible to a single static constraint on the entire database. I have, however, come up with a use case that seems plausible but cannot be so reduced.
Consider the classic employee-department database. The business rules are:
- Every employee must belong to exactly one department
- When a department is created, it can exist only on paper, with no employees yet
- A department which loses its last employee ceases to exist.
Rule 1 is no problem: the emp relation has an attribute dept_id and the constraint is simply that every emp.dept_id is equal to some dept.dept_id.
Rule 2 means that it must be possible to insert a department without any employees. Indeed, by Rule 1 nothing else is possible, since an employee without a department can't even exist.
Rule 3 means that a department tuple cannot be deleted if there are any joinable employee tuples.
So Rules 2 and 3 taken jointly constitute a constraint on the history of the dbvar rather than on its current state. Initially, a department without employees is valid, but once it has, or has had, employees, it can no longer exist without them. That can't be reduced to a simple mathematical constraint on the whole dbvar. On the other hand, it's easy to model as constraints on insert and delete actions.
How should such a thing be handled in TTM?
Is this not what's covered by RM Very Strong Suggestion 4 -- transition constraints?
I would argue that this is a spurious set of constraints, of no business value, and an unnecessarily complex illustration of a point barely worth making.
In a business setting, a decision has to be made whether a department with no employes may exist, or not, regardless of history.
- If it can, the problem does not arise.
- If it cannot, then a new department can only be created by also adding its first employee, presumably using multiple assignment as per RM Pre 21.
If the intention is to illustrate the need for transition constraints, then this example is spurious. For example:
- Given an attribute of type marital with values (never,married,widowed,divorced)
- The value never is permitted, but a transition from any other value to never is not.
A static database constraint cannot express that. It requires RM VSS 4.
Quote from dandl on August 2, 2019, 12:53 amI would argue that this is a spurious set of constraints, of no business value, and an unnecessarily complex illustration of a point barely worth making.
And yet, somehow, worth replying to? As it happens, it is not spurious, because it was actually the case at my employer of yore. I know, because I was one of the programmers on it.
In a business setting, a decision has to be made whether a department with no employes may exist, or not, regardless of history.
And yet.
- Given an attribute of type marital with values (never,married,widowed,divorced)
Is that example drawn from life? The systems I'm familiar with have single, not never. At any rate, Dave's response was sufficient.
Quote from johnwcowan on August 1, 2019, 4:21 pmIf I understand correctly, all TTM constraints are meant to be reducible to a single static constraint on the entire database. I have, however, come up with a use case that seems plausible but cannot be so reduced.
Consider the classic employee-department database. The business rules are:
- Every employee must belong to exactly one department
- When a department is created, it can exist only on paper, with no employees yet
- A department which loses its last employee ceases to exist.
Rule 1 is no problem: the emp relation has an attribute dept_id and the constraint is simply that every emp.dept_id is equal to some dept.dept_id.
Rule 2 means that it must be possible to insert a department without any employees. Indeed, by Rule 1 nothing else is possible, since an employee without a department can't even exist.
Rule 3 means that a department tuple cannot be deleted if there are any joinable employee tuples.
So Rules 2 and 3 taken jointly constitute a constraint on the history of the dbvar rather than on its current state. Initially, a department without employees is valid, but once it has, or has had, employees, it can no longer exist without them. That can't be reduced to a simple mathematical constraint on the whole dbvar. On the other hand, it's easy to model as constraints on insert and delete actions.
How should such a thing be handled in TTM?
John, your example is absolutely no trouble at all. Anything you can express with words, if logically self-consistent, can be expressed as a single static constraint on the entire database.
The best kind of database schema is one whose canonical relvars are self-auditing log-like insert-only. The records all represent immutable statements describing events or states of entities at particular points in time or within a sequence. The records do not represent mutable statements describing current states only. As such, anything can be defined as a single static constraint on the entire database.
Anyone who disagrees with me, say why and how. Anyone who agrees with me, I appreciate your saying so.
Quote from johnwcowan on August 1, 2019, 4:21 pmIf I understand correctly, all TTM constraints are meant to be reducible to a single static constraint on the entire database.
Well no. RM VSS 3 "D should support transition constraints" They can't be merely ANDed with the static constraints.
I have, however, come up with a use case that seems plausible but cannot be so reduced.
No this doesn't seem plausible. I'll explain below.
Consider the classic employee-department database. The business rules are:
- Every employee must belong to exactly one department
- When a department is created, it can exist only on paper, with no employees yet
- A department which loses its last employee ceases to exist.
Rule 1 is no problem: the emp relation has an attribute dept_id and the constraint is simply that every emp.dept_id is equal to some dept.dept_id.
Rule 2 means that it must be possible to insert a department without any employees. Indeed, by Rule 1 nothing else is possible, since an employee without a department can't even exist.
Rule 3 means that a department tuple cannot be deleted if there are any joinable employee tuples.
Hmm? It's rule 1 that says that. Rule 3 says that a department tuple must be deleted under certain circumstances. Are those circumstances recorded in only one way, namely as the value of attributes in tuples in the database? No, they were recorded, and got un-recorded. I think you're on a sticky wicket.
So Rules 2 and 3 taken jointly constitute a constraint on the history of the dbvar rather than on its current state.
You might do it that way, then as per Dave's answer, Transition Constraints is the official tool.
Myself I have a constitutional aversion to Transition Constraints. I would require sufficient history to be permanently recorded that the business requirement can be expressed as a static constraint. In practice it would be daft to delete a department record (or any referenced-to datum) after it has been referenced-to. (I might allow it if it were never referenced-to, for example a department set up in error.) I'd instead record a date-ceased in an auxiliary relvar.
If the last employee ceases to be (an employee) unexpectedly, that doesn't sufficiently cause the Department to cease to be: it still exists on paper, as per Rule 2.
Of course you can make up some business rule contrary to that, for the sake of perversity. But you can't claim it to be a "use case", nor to be "plausible".
Ah, I've just seen this in John's reply to David B
As it happens, it is not spurious, because it was actually the case at my employer of yore. I know, because I was one of the programmers on it.
Was this a "classic employee-department database" as it said on the tin? Was this a database used for running payroll/allocating budgets/maintaining employment records? As required both legally and by auditing rules? Then deleting Departments and their associated financial and employment history was illegal, and your employer of yore was in breach of rather a lot of regulations.
Or was this some auxiliary database, where the pay/employment stuff was recorded elsewhere and the Department not in fact deleted?
Or was this in fact not a classic Department, but some sort of employee grouping/reporting unofficial not-really-a database?
I'm inclined to agree with David B this is some "spurious set of constraints". There's a lot of questions of this nature on StackOverflow ('How do I design a schema for this?') from junior programmers who are not Business Analysts, and don't know the whole business context, and likely are trying to serve some under-the-counter reporting request from a business user who also doesn't understand the context. A professional's role is to a) refer the user to somebody who does know the context, not try to answer it themselves; b) if in fact it is the BA's design, to advise of the dumbness of deleting referenced-to datums after they've been referenced.
Quote from Darren Duncan on August 2, 2019, 4:36 amQuote from johnwcowan on August 1, 2019, 4:21 pmIf I understand correctly, all TTM constraints are meant to be reducible to a single static constraint on the entire database. I have, however, come up with a use case that seems plausible but cannot be so reduced.
Consider the classic employee-department database. The business rules are:
- Every employee must belong to exactly one department
- When a department is created, it can exist only on paper, with no employees yet
- A department which loses its last employee ceases to exist.
... So Rules 2 and 3 taken jointly constitute a constraint on the history of the dbvar rather than on its current state. Initially, a department without employees is valid, but once it has, or has had, employees, it can no longer exist without them. That can't be reduced to a simple mathematical constraint on the whole dbvar. On the other hand, it's easy to model as constraints on insert and delete actions.
How should such a thing be handled in TTM?
John, your example is absolutely no trouble at all. Anything you can express with words, if logically self-consistent, can be expressed as a single static constraint on the entire database.
No. It can be expressed as a static constraint only if it can be expressed in terms of the value of attributes in relvars currently in the database. This example is specifically to do with values that were previously in the database but aren't currently.
Anyone who disagrees with me, say why and how.
Darren I disagree. See my answer.
Quote from AntC on August 2, 2019, 4:46 amQuote from Darren Duncan on August 2, 2019, 4:36 amQuote from johnwcowan on August 1, 2019, 4:21 pmConsider the classic employee-department database. The business rules are:
- Every employee must belong to exactly one department
- When a department is created, it can exist only on paper, with no employees yet
- A department which loses its last employee ceases to exist.
How should such a thing be handled in TTM?
John, your example is absolutely no trouble at all. Anything you can express with words, if logically self-consistent, can be expressed as a single static constraint on the entire database.
No. It can be expressed as a static constraint only if it can be expressed in terms of the value of attributes in relvars currently in the database. This example is specifically to do with values that were previously in the database but aren't currently.
Anthony, the stated business rules can be expressed entirely with a static constraint on a database whose present value contains the business history, which is what I described. Nothing in the business rules said the history can't all be retained, they didn't say history had to be deleted. So how do you think my stated solution is not valid? -- Darren Duncan
Quote from johnwcowan on August 1, 2019, 4:21 pmIf I understand correctly, all TTM constraints are meant to be reducible to a single static constraint on the entire database. I have, however, come up with a use case that seems plausible but cannot be so reduced.
Consider the classic employee-department database. The business rules are:
- Every employee must belong to exactly one department
- When a department is created, it can exist only on paper, with no employees yet
- A department which loses its last employee ceases to exist.
Rule 1 is no problem: the emp relation has an attribute dept_id and the constraint is simply that every emp.dept_id is equal to some dept.dept_id.
Rule 2 means that it must be possible to insert a department without any employees. Indeed, by Rule 1 nothing else is possible, since an employee without a department can't even exist.
Rule 3 means that a department tuple cannot be deleted if there are any joinable employee tuples.
So Rules 2 and 3 taken jointly constitute a constraint on the history of the dbvar rather than on its current state. Initially, a department without employees is valid, but once it has, or has had, employees, it can no longer exist without them. That can't be reduced to a simple mathematical constraint on the whole dbvar. On the other hand, it's easy to model as constraints on insert and delete actions.
How should such a thing be handled in TTM?
TTM has nothing to say on how a db designer should be designing his databases, and that makes that final question relatively unanswerable.
There is no "single right way" of how these things "should" be handled in a TTM-compliant ecosystem, beause TTM does not prescribe that temporal features (such as those per TDaTRM/TaRT) must be supported. But you do need those features to solve this as a "static" (as you call it) database constraint (and design your db to keep the needed history).
Your rules are not saying "departments cannot exist without employees", rather they are saying "there cannot be two distinct periods in time when a department exists without employees" (assuming such "distinct periods in time" are not immediately consecutive). So e.g. in SIRA_PRISE, you'd define the temporal query yielding all such periods in time, define that as a virtual relvar, and then declare that virtual relvar as subjected to a key {DEPT_ID}.
(Note that "keep the needed history" here implies that the assignment of employees to departments has to have been be singled out into its own -historical- relvar, so this in itself might already mean we're no longer talking of "the classical employee-departments database").
In a system not supporting those temporal features indeed you'd have to perform a check like "would this unassignment (of employee to department) leave the concerned department empty" (and consider carefully ***when*** you are going to do that - consider e.g. the business transaction might still resolve the situation later by assigning some other employee to the now-empty department).
So this is not a question of TTM vs non-TTM system, it's a question of what are the semantics of the situation and how can we use the available features of our engine to capture/support/implement the thing satisfactorily (or turned around, which features do we need and which systems exist that offer us those features, aka, "choosing the right tool for the job").
Quote from Darren Duncan on August 2, 2019, 6:50 amQuote from AntC on August 2, 2019, 4:46 amQuote from Darren Duncan on August 2, 2019, 4:36 amQuote from johnwcowan on August 1, 2019, 4:21 pmConsider the classic employee-department database. The business rules are:
- Every employee must belong to exactly one department
- When a department is created, it can exist only on paper, with no employees yet
- A department which loses its last employee ceases to exist.
How should such a thing be handled in TTM?
John, your example is absolutely no trouble at all. Anything you can express with words, if logically self-consistent, can be expressed as a single static constraint on the entire database.
No. It can be expressed as a static constraint only if it can be expressed in terms of the value of attributes in relvars currently in the database. This example is specifically to do with values that were previously in the database but aren't currently.
Anthony, the stated business rules can be expressed entirely with a static constraint on a database whose present value contains the business history, which is what I described. Nothing in the business rules said the history can't all be retained, they didn't say history had to be deleted. So how do you think my stated solution is not valid? -- Darren Duncan
You said
The best kind of database schema is one whose canonical relvars are self-auditing log-like insert-only.
That's a kinda motherhood-and-apple-pie statement. If you meant it to be a description of your 'solution', it was abundantly not clear.
So you appear to agree with me that John's originally stated business requirements (3 points) are not plausible(?) John specifically asked about a constraint on the "history of the dbvar rather than on its current state" -- explicitly saying the design did not retain history. You seem to have answered a different question, by implying (apparently, but failing to specify) a different design of the database.
Whatever.