Robert Haas wrote: > I sort of think that this kind of stuff belongs in a separate table > somehow. For example in this case I would want to: I wasn't suggesting otherwise. A constraint is a constraint; whether it involves an extra table or not wasn't really relevant until now (apparently). > CREATE TABLE attack_probability ( > attacker_type_id integer not null references animal_type (id), > victim_type_id integer not null references animal_type (id), > percent_chance integer not null, Personally I would prefer something that can hold decimals, a numeric fe. And I usually add cascading behaviour definitions explicitly. > primary key (attacker_type_id, victim_type_id) > ); > > ...and then declare that the CONSTRAINT on the "maulings" table is that > if I look up the types of the attacker and victim, that pair of types > must be present in the attack_probability table with percent_chance > 0. This was pretty much what I had in mind. Not _exactly_, as I didn't concern myself with the implementation details yet. But it pretty much boils down to what you suggested, yes. > I guess my point here is that I think in your proposal you are letting > domain-specific data creep into the schema. It's the job of the schema > to enforce integrity constraints, but not to know specifically how > things work. The fact (if it is a fact) that the chance of one type of > animal attacking another can be captured as a probability (rather than, > say, one probability for the day time and another probability for the > night time, or one probability for each specific animal rather than each > animal type, or I don't know what's going on and want to infer the > probabilities from the data after I've gathered it) is domain-specific. > I don't really want the information about attack probabilities (or > whatever) to be something that's hardcoded in my schema; I want it to be > part of the data in the schema, with the schema enforcing such > constraints on that data as I may see fit to define. I don't want to > have to select things out of system tables to find out attack > probabilities. Also, as a practical matter, I suspect that such a setup > would result in an absurdly complex constraint language. I was merely pointing out the possibility of such a constraint - maybe "business rule" is more appropriate (but isn't that a constraint as well?). In the original suggestions this wasn't possible, it simply stated that wolves cannot attack lions - which isn't necessarily true. I suppose that's the point I was trying to make in general; the whole who-attacks-who business isn't that black-and-white - more like different shades of gray. In the end you'll have to make a decision about what combinations are possible depending on what you want to get out of your database. It may not be necessary to get into this much detail - or it may. Not for me to say. > ...Robert -- Alban Hertroys alban@xxxxxxxxxxxxxxxxx magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
