Greetings again, and thanks for viewing yet one more noob inquiry.

(I've attached a jpeg file with erd.)

I have three tables -- operators, haulsystems, and harvestsystems. My business rules dictate that: a. any given operator does not need to be entered into the haulsystems table AND the harvest systems table. But, b. the operator must be entered in either the haulsystem table OR the harvestsystem table. If an operator is not associated with either a haulsystem or a harvestsystem, then that operator will not appear in the operators table.

The relationship between operators and haulsystems is one-to-many and the participation of operators with haulsystems is optional, while the participation of haulsystems with operators is mandatory. (An operator does not have to be involved with haulsystems -- conditionally -- but a haulsystem does have to be involved with an operator -- and only one operator.) The same relationship exists between operator and harvestsystem.

So, how do I logically handle this condition, given that participation is "sort-of" or conditionally optional... and (thereby) conditionally mandatory? Again, thanks.

In SQL if you enforce such conditions then in most cases the tables cannot be updated. Standard SQL does not permit simultaneous updates involving multiple tables and therefore it is not feasible to enforce a constraint that a value must always exist in Table A and some other table(s). Because of this limitation referential constraints in SQL are usually designed to be optional at one end or the other - even where the business rules imply otherwise.

As a workaround there is something called a "deferrable" constraint, which is a constraint that can be turned off for the duration of a transaction. This is not ideal in every case but if your DBMS supports it then it is a workaround.

For now, I'll keep participation optional on between operators and children. Then, later I'll rethink constraints and delve into the sql topics you've brought up. Once I'm solid on a revisable design, I'll directly plunge into sql.

Many thanks.