Optional Relationship

The determination of optional -vs- mandatory and 1:1 -vs- 1:N -vs- M:N are independent of each other. That is, you can have relationships that are mandatory each way or optional each way regardless of the number of related records in each entity.

For example, the spousal relationship (in America) between the table of adult males and the table of adult females is 1:1 and optional each way. A man can be married to one woman, but he doesn't have to be married at all. The same is true for women. Marriage is not mandatory. The 1:1 notation simply means that bigamy is illegal in America.

The dating relationship between males and females is M:N and optional both ways. People can date as many people as they please (hence the M:N notation). People do not have to date at all (i.e., it is optional for both sexes).

The purchasing relationship between customers and products is M:N. It is mandatory "from customer to product" (i.e., a customer must buy at least one product ... or they aren't really a customer). The purchasing relationship is optional "from product to customer" (i.e., bad product ideas may never sell). Would you pay $50 for an 8 ounce jar of chocolate-covered pickles? Would anyone pay that much? HEB could have a dozen jars on their shelves (so chocolate-covered pickles is a legitimate product), but no idiot will ever pay $50 for a jar (so they are never part of the purchasing relationship between customers and products).

The relationship between employees and paychecks is 1:N. It is optional "from employee to paycheck," but mandatory "from paycheck to employee." An employee doesn't have to have any paychecks (i.e., paychecks are optional). But, every paycheck must have an employee (i.e., payees are mandatory).

More FAQs