The ORM Foundation

Get the facts!

Exclusion constraints

Last post 03-19-2014 12:29 by Ken Evans. 41 replies.
Page 3 of 3 (42 items) < Previous 1 2 3
Sort Posts: Previous Next
  • 03-18-2014 5:04 In reply to

    Re: Exclusion constraints

    Hello Ken,

    Many things stored in databases are beliefs.This is why update functionality exists in the first place. There are a few examples where a database is the primary fact holder, general ledger comes to mind, and in such cases update functionality is prohibited. Instead of changing a line in a general ledger we create a new entry negating the one we want corrected and add yet another entry to make the correct statement.

    Most facts in a database, however are hearsay. We believe, Jim Stefford is called Jim Stefford, because he filled that out on his application form and we believe he correctly spelled his name, or we derived it from his ID-card, or passport he showed upon application, believing it was correctly spelled on those documents, and believing we made no errors copying it.  

    The holder of that belief can either be implicit (the organization owning the database) or it can be explicit, as is customary in several audit-trail implementation where the user adding a statement to the database is explicitly stored with the statement.


  • 03-18-2014 7:28 In reply to

    Re: Exclusion constraints

    Hello Niels,

    Here is a model that (I hope) explains what I am trying to say.
    Which is that a belief about a fact is not that same thing as the fact itself.

    Although this example is about job start and end times, the model is a general model that can be applied to any fact.

    A person utters a fact. (By "utter" I mean that the person either speaks or writes the fact)

    * The person who utters the fact (the utterer) may believe that the fact is true or the utterer may believe that the fact is false.
    * Other people who hear or read the fact may believe that the fact is true or they may believe that the fact is false.

    Thus, belief about an uttered fact is independent of the uttered fact itself.

    Hope this helps


    PS: This raises the interesting question "Can it be the case that an organization (aka a legal person) can be said to 'believe' a fact?"
    One argument might be that "If an organization has a record of a fact then the organization can be said to 'believe' that the fact is true." 

    But "organizations" are abstract concepts that refer to agreements between people.
    So exactly who is this "organization" that "believes" that an institutional fact is true?

    Doesn't it all come down to the "fact" that one or more natural persons believe that the fact in question is true?

    Filed under:
  • 03-18-2014 11:59 In reply to

    Re: Exclusion constraints

    Hello Ken,

    We're getting really philosophical here. Does there exist something like a "naked" fact? Even if we say that a fact and its existence are one and the same, we already have to introduce an additional fact to express the knowledge of that fact. It is even debatable whether a fact can exist without someone knowing about it. Is a fact something that directly exists in the physical world, or is it a mental construct? Even if we decide it is the former, then we still need to lift it into a mental construct to have knowledge of it.

    When working with databases, every fact we store, is by definition a compound fact. It consists of the formulated fact, and the fact that it is stored in the database. 

    When adding a transaction period to a record in a database, we make that storage explicit, we say when it started, and if for some reason we no longer want to support that exact fact, we register when we "deleted" it. That deletion is of course only in a logical sense, since we need to physically keep the fact stored to even learn about its deletion.

    Nearly every fact is temporal, whether we care about its temporal quality or not. Sometime 13+ billion years ago, none of the fact we care about now were even definable, let alone have a definite truth value. A statement like the earth revolves around the sun, only makes sense once we can define "earth"  and "sun". Entities need to at least have existed for us to associate a fact with them, so facts generally have a start time associated with them. A fact also needs to be known before it is of any value to us, so every fact we deal with has (whether we know its value or not) an extra start time, reflecting the moment we started to know about it.

    The point I am trying to make is that the fact itself and it's validity and transactional aspects are intricately interwoven with one another. That doesn't mean we can't and shouldn't treat temporal facts as composite (teasing out the fact itself from its validity and transactional aspects). In fact, I wouldn't even mind if ORM had a simple flag where the fact itself can be marked as having a validity range and a transactional range and let the DDL generators do the tedious work for us. 

    Personally, I don't care much about the question whether an organization can believe something. If a fact is stored in a corporate database then the organization believes it to be true. Maybe I am cutting corners here, but if a fact is stored in a database, then there are natural persons who believe it to be true. Even if the fact is obviously wrong to nearly everyone working in the organization then there still is a DBA who deems it correct :-).


  • 03-18-2014 12:23 In reply to

    Re: Exclusion constraints


    Clifford Heath:
    Ken, yours may be a fair comment in many cases


    It was not my intention to make a "fair comment." 
    My intention was to assert propositions about the nature of the relationship between "facts" and "beliefs".


    a)  A belief cannot exist in the absence of a person who expresses (or has expressed) the belief in the form of a fact.
    b)  The fact that the person is asserting may be either true or false.
    c)  Zero or more persons may believe that the fact is true.
    d)  Zero or more persons may believe that the fact is false. 


    For example:
    Some person might express a fact such as "Neil Armstrong set foot upon the Moon in 1969"
    The person who expressed the fact (and others) may believe the fact, or disbelieve the fact.
    In other words:
    a) All beliefs are about facts (aka propositions)
    b) The subject matter of any particular fact has nothing to do with "fact" that a "belief" is a belief about the truth or falsity of one or more facts.


    Filed under:
  • 03-18-2014 13:56 In reply to

    Re: Exclusion constraints

    Hi Niels,
    Yes - it is all about Philosophy - where I use the term to mean "The practice of making sense of the world".

    Niels Hoogeveen:
    Does there exist something like a "naked" fact?

    I would say "no" - Facts only exist because they have been asserted by a person.
    (A fact maybe the output of an algorithm in a program, but the algorithm was written by a human being.)

    Aha! you might say. What about gravity? Surely that is a "naked fact"?
    Well I say "no" because although gravity itself existed long before the planet Earth was formed, what we are concerned with is "facts".
    And facts are propositions expressed by some person in some language.
    For example it was a human being who devised the formula:  F = Gm1m2/r2
    (But we still don't know what gravity "is")


    Niels Hoogeveen:
    Is a fact something that directly exists in the physical world, or is it a mental construct?

    I hold the view that a "fact" is nothing more or less than a proposition that has been expressed by some person in a language. 


    Niels Hoogeveen:
    It consists of the formulated fact, and the fact that it is stored in the database. 

    At what Terry calls the "conceptual level" there is no notion of how facts are stored.
    Regardless of whether a fact is spoken, written on paper or stored in a database - it still remains a fact that has been expressed by some person in a language. As I see it, the mode of storage and manipulation is quite independent of the facts themselves - which are just utterances by humans in a language.


    Niels Hoogeveen:
    Nearly every fact is temporal

    It is true that people can assert facts such as "The present King of France is Bald".
    And there may have been a time when this fact was true.But if stated in 2014 it is meaningless because France does not have a King.

    So for me, the "temporal aspect" resolves into a simple question: At time x, was this proposition True, False or Meaningless?

    You can also express "facts about beliefs" such as: At time x, person y believed that proposition z was either True, False or Meaningless.


    Niels Hoogeveen:
    The point I am trying to make is that the fact itself and it's validity and transactional aspects are intricately interwoven with one another.

    Well forgive me, but I don't see any "intricate weaving" going on. I just see a set of propositions that can be uttered (and maybe stored.) and that may be related in some way to other propositions. (for example as they are in an object-role model)

    Fabian Pascal's words come to mind:  

           A DBMS then, is a deductive logic system: it derives new facts from a set of asserted facts. The derived facts - query results - are true if and only if:
           * the initial assertions are true
           * the derivation rules are logically sound and consistent.

    Now I don't disagree with your general observations about the state of "corporate databases" and related matters. However, there is a lot of evidence to support the proposition that "Many 'corporate databases' are not well designed and so (to use Fabian Pascal's phrase) their derivation rules are unlikely to be sound and consistent." Furthermore, in some cases, the way that the "initial assertions" are stored makes it extremely difficult to check whether the facts are true, false or meaningless.

    My reason for banging on about facts such as "Organisation x believed fact y at time z" is that it is an example of a meaningless proposition because an organisation cannot believe anything. Only people can believe things and the things that they believe or disbelieve are expressed as facts in a language.


    Filed under:
  • 03-18-2014 16:24 In reply to

    Re: Exclusion constraints


    You are using the word "belief" in a different sense than I intended. Your ORM model only allows one JobStart for a given person and organisation (which already over-simplifies - a person may have had multiple periods of employment with the same organisation). But then you model whether a specific Person believes that or not. That may be true, but it's pretty irrelevant to the discussion and not what I intended. In addition, your model cannot meet the business need for a forensic timeline - so you diverted the argument.

    You also latched onto Niels word "stored". Storage is not the issue he was intending to address, and you were wrong to relegate it to the sub-conceptual level.

    When a fact is added to a population of facts (which is what Niels intended by "stored"), that addition reflects the acceptance of that fact as being a true statement of affairs. The business process that led up to that fact being accepted (or "believed") is a reflection by the organisation of that business process being authorised to make that kind of acceptance. In this respect, the organisation does believe the fact - it trusts the process by which it was decided to admit the fact to the population of accepted facts. Other players in the business are then entitled to act as if that fact is true - to believe the fact. Therefore I take issue with your assertion that an organisation cannot believe things. The belief is in peoples' heads, but the database is a means of sharing those beliefs.

    Whether the model is a true map of the complexities of the "real world" is also irrelevant. No model is perfect. Even the idea of "an individual" is a mental construct that does not fully accord with physical reality - my quantum states are inextricably linked with every particle in the universe, I'm not isolated. There are always flaws in any model, or it wouldn't be a model. The acceptance of certain facts into a model is done with an awareness that not only the fact, but the model itself is a simplification, and either may also be incorrect. But this again is a diversion.

    So we come back to the meat of this discussion, which you haven't even mentioned. When a population of facts is found to contain an incorrect fact, it's often necessary (for legal, organisational or operational reasons; discussion of these reasons is also a diversion) to:

    1. correct the fact
    2. retain a record of the period over which the incorrect fact was accepted
    3. record who the fact was corrected by, why it was wrong, and/or why the new fact is to be trusted instead
    4. correct the consequences of the incorrect fact having been accepted

      In the case under discussion, there are two types of facts to which this applies; JobStart and JobEnd. These usually occur in pairs, and the pairs construct a time-line of events consisting of a collection of periods of employment.
      This thread has been about the best way to model these periods (periods over which a fact was accepted, and periods over which a person was employed), and about how best to express the constraints over correct populations of such information. Please do try to address this problem, and not create any further diversions running off on interesting but irrelevant philosophical tangents. Or at least, start a different thread to discuss your theories of belief or of the faults in organisational information systems.

    1. 03-18-2014 16:30 In reply to

      Re: Exclusion constraints

      Hi Ken,

      We are in full agreement with respect to gravity. It's not a fact, which can easily be shown since it is a noun. Gravity makes the Earth rotate around the Sun, is a fact, just as a falling Apple made Newton contemplate about gravity, is a fact. Whether an apple actually fell on his head may be an untrue fact.

      Now to your definition of fact:

      I hold the view that a "fact" is nothing more or less than a proposition that has been expressed by some person in a language.  

      Let's look at two propositions expressed by some person in a language:

      Ken said: "Lee Harvey Oswald killed John F. Kennedy" 

      Niels said "Lee Harvey Oswald vermoordde John F. Kennedy" 

      Note: "vermoordde" is the Dutch equivalence of "killed", and in this particular context has exactly the same meaning.

      Obviously the two statements are not equal, one is expressed by a person named Ken, the other by a person named Niels, and they are even expressed in different languages, yet they both make the same factual statement about the killer of John F. Kennedy.

      I would say therefore that a fact is nothing more and nothing less than a proposition, irrespective of who said it, and in what language it is stated.

      killed(Lee Harvey Oswald, John F. Kennedy)  = "Lee Harvey Oswald killed John F. Kennedy" = "Lee Harvey Oswald vermoordde John F. Kennedy" 

      That said, the statement "Lee Harvey Oswald killed John F. Kennedy" is only a proposition in particular contexts, and within those contexts can even have different truth values.

      In the here and now it is a proposition, and we may say it to be true (depending on whether one accepts the official reading or not).The statement is also a proposition, but pertinently a false one in a context between 18 October 1939 and 22 November 1963. Before 18 October 1939, the day Lee Harvey Oswald was born, it is not even a proposition because the term Lee Harvey Oswald is undefined.

      Even though a fact is independent of the person stating it and also independent of the language it is stated in, it is not independent of the context in which it is stated.  

      Sometimes the context in which a proposition is true is kept implicit, but sometimes we want to be very explicit about these contexts and at least have them supported in the database we are going to store facts in. 

      Since we can derive a database schema from ORM's conceptual model, it would be nice if we could either model these contexts, or at least mark facts as applying to a certain context.

      As to your statement about whether organizations believe something to be true, we can easily fix that by saying the CEO of an organization believes something, and if he doesn't personally have any beliefs about a fact, he has delegated that to someone else in the organization. Therefore the highest ranking person holding a certain belief about a fact represents the organization and with that gives the organization a particular belief. 




    2. 03-18-2014 16:59 In reply to

      Re: Exclusion constraints

      Niels Hoogeveen:
      Gravity makes the Earth rotate around the Sun, is a fact

      Hah. The earth actually travels in an almost perfectly straight line defined by a non-Euclidian geometry, as with the motion of all bodies moving freely. I say "almost" because the solar wind and radiation does apply a non-zero force. The mass of the earth and other bodies determines the distortion that makes space non-Euclidian, so straight lines appear bent, and that phenomenon is called gravity. So much for facts, eh?

      Regarding organisational belief, it's not always the highest-ranking person who determines the organisational belief. Often the most senior folk have beliefs that the rest of the organisation know are barmy. That's why I formulated it in terms of the organisation's beliefs consisting in their sanctioning the process that accepts facts into the database.

    3. 03-18-2014 17:15 In reply to

      Re: Exclusion constraints


      Both your descriptions are more accurate than mine.

      I tend to be a bit hand wavy at times when dealing with people. 

      That said, I would like to see the discussion return to how we model bi-temporal fact (a fact having a validity period and a transaction period) in ORM, such that a mapping procedure has all the necessary information to, at least in principle, be able to create the desired DDL expression.


    4. 03-19-2014 2:21 In reply to

      Re: Exclusion constraints


      Since this is more of a "how to" than an "extension to ORM" I have started this new thread in the ORM Techniques section.
      Please will you make a "re-start" post in the new thread that explains what you want to discuss. 



      Filed under:
    5. 03-19-2014 4:57 In reply to

      Re: Exclusion constraints

      Hello Ken,

      Bitemporal modelling is closely related to exclusion constraints, a feature not directly supported in ORM. The bitemporal example I gave in this thread, uses two ranges, one to indicate the validity period of a fact and one to indicate the transaction period of a fact.

      As we established earlier in this thread, ranges are not necessarily easily modeled in ORM, but are crucial for a proper implementation in databases that have range types and exclusion constraints. At this moment PostgreSQL is to my knowledge the only database having these features, but we may expect other vendors to eventually support these as well.

      The example given works as follows:

      We want to register in a database the employment history of people. We therefore have entities Person and Organization, which both, for simplicity sake have an id and a name and no other attributes, and we have some means to store the actual employment history.

      With respect to the employment history we note that people can work for the same organization more than once, but these employments are nor allowed to overlap.

      An employment describes a period, a range of date-time values having a lower bound and upper bound. The employment period should have an inclusive lower bound and an exclusive upper bound, such that the ranges t1 to t2 and t2 to t3 do not overlap. The value t2 belongs to the second range because of the inclusivity of the lower bound and does not belong to the first range because of the exclusivity of the upper bound.

      An employment periods should also be able to have an open ended upper bound to reflect employments that have not ended yet.

      Furthermore we don't want to allow updates and deletes of employment facts, so we introduce a transaction period, a time frame during which a fact is held to be true. The bounds of the transaction period are analogous to that of the employment period. It also has an inclusive lower bound, an exclusive upper bound and the upper bound may be open to reflect those fact that are held to still be true.

      Transaction periods are not allowed to overlap for employment facts having the same person, organization and overlapping employment periods. 

      With the inclusion of transaction periods, employment periods for the same person and organization are allowed to overlap, as long as transaction periods are non-overlapping.

      In PostgreSQL we would define the latter as an exclusion constrain, where person and organization are compared using the "=" operator, and employment period and transaction period are compared using the "&&" operator. The latter operator stands for overlap. 

      In the interest of this thread, and potential extensions to ORM, I would like to know how to express bitemporal facts in ORM, and how to express this in such a way that we can easily reuse this pattern in a model that has many bitemporal facts.  


    6. 03-19-2014 12:29 In reply to

      Re: Exclusion constraints


      Niels Hoogeveen:
      We are in full agreement with respect to gravity. It's not a fact, which can easily be shown since it is a noun.

      Oops - my poor use of language.
      What I should have said it was not until about 1686 that a human observer uttered the proposition that:  

      "Any two bodies in the universe attract each other with a force that is directly proportional to the product of their masses and inversely proportional to the square of the distance between them"  (or as now used: F = Gm1m2/r2.  for short.)

      However, the phenomenon of bodies attracting each other had been going on for more than 13 billion years before a human being uttered this proposition.

      So, the purpose of my comment about gravity was to begin to establish a foundation for the relationship between events and facts. 

      However, as both Clifford and you have observed, it is a philosophical point and is separate from the bi-temporal modeling issue.

      So, since I believe that a philosophical perspective is essential to understanding the nature of data modeling, I have started a this new thread to continue the discussion about the "philosophical" aspects of facts. Questions for discussion include "Who uttered the fact? When did they utter it? What are the beliefs about each fact? Can an 'organisation' be said to 'believe' a fact?" and so on.

      I have some urgent technical work to do on the website so I'll get back to the bi-temporal debate in the next 24-48 hours.


    Page 3 of 3 (42 items) < Previous 1 2 3
    © 2008-2014 The ORM Foundation: A UK not-for-profit organisation -------------- Terms of Service