Home Forums Database Design Relational Theory Defining keys as sets that must not intersect instead of scalar values that must not be equal RE: Defining keys as sets that must not intersect instead of scalar values that must not be equal

  • "because you appear to say that there's no such thing as a Person"*

    You're mistaking the employment agreement for the person. The key for the person, whatever that might be, is required as part of the identification of the employment agreement because the candidate key for the employment agreement consists of both a set of moments in time and a person. Per my now-repeated claim, it is possible to still have a multi-column key. The employment agreement is an example of something which requires a multi-column key. The person part of the key groups the set of intervals in the intervals part of the key. But the employment agreement itself is identified by the combination of the two.

    I'm not sure whether you're actually comprehending the suggestion. I've tried to be quite clear, and I think sentences like "this set of {date, person} tuples is the natural key for the employment agreement" cannot be further clarified, so I'm not sure what more I can do about that. Perhaps I can come up with a completely way of demonstrating the idea. But I must also suggest that more careful consideration of what I've written might also help. I will try to improve my illustration.

    "since a date is a point in time, not an interval (unless you have storage and processing time to burm, and are willing to have 7,305 dates insteaa of just two to cover a 20 year emploment for perhaps each of a few thousand employee"

    Tom, this seems deliberately obtuse. Two dates which represent the endpoints of an interval equivalently represent a set of moments in time. I am simply going to assume that you don't disagree with this, because if you do then I don't know what you think a set is, and the discussion is pointless.  I can, in general, represent a set either extensionally or intensionally. In the case of moments in time an extensional definition is infeasible (indeed, it is impossible if we are referring to reality as opposed to a model on a computer, where it is simply infeasible). I am not suggesting that we actually should turn the "start date and end date" representation into an extensionally defined set. I am simply demonstrating that what is being described is a set, which can, in principle, be represented by an extensional definition in an obvious, though infeasible, way.

    You seem to think I am suggesting that we must implement this idea by storing the infinite set of {moment-in-time, person} tuples (which we obviously agree is impossible), or at least the huge finite set of all measurable moments in time (down to the plank time) or at least the very large finite set of { moment-in-time-to-the-maximum-granularity-available-in-the-engine, person } tuples, which we agree is infeasible.. I don't know why you think that.

    Let's put it as simply as this: If you agree that an employment agreement could be represented as { person_id, start_date, end_date, [...], primary key (person_id, start_date, end_date)), notwithstanding the objection that this key doesn't quite do what we need it to do, which you will recall is my objection, then you agree that the key for the employment agreement is the combination of the person and a set of moments in time. It's that simple.

    "On the other hand, employment by two different employers can overlap on dates in various special circumstances"

    Sure, I can imagine some model where we would need to allow that. In my example model I don't need to allow that. This seems to be a common theme... people take an example which uses a model designed to demonstrate a principle, and then claim an objection on the basis that some other models can also exist. Of course some other models can also exist. We could talk about a model of retail sales, or ornithology, or MMORPGs, where this sort of situation may never appear. But if this sort of situation doesn't appear then there'd be little point in my using it as an example model to demonstrate the situation. It's an argument which is formally the denial of the antecedent. I claim that a given model implies a certain idea. You then claim that other models exist, and that this is an argument against the idea. This is an argument of the form A -> B, !A, ?!B.

    Yes, I have read several of the works of Codd, Date and Darwen back as far as the Turing lecture, but not prior to that. I have read no published works by Fabian Pascal other than what commentary he has freely made available online.