One Child, Two Parents where only one creates, the other adopts
I'm a little puzzled about something. Lets say you have a list of line-items created by an invoice. Like, Invoice and LineItem are both tables where the LineItem is a child table. Another table, Process, will subsequently be related to a line-item record, only in this case, we're not allowing the Process layout (based on the Process table) to cause a line-item record to be created. Since the LineItem is a join table between the Process and Invoice tables, it makes sense to allow only one table to initiate the creation of a record in the LineItem table. So relating a record between LineItem and Process is done by assignment, and the LineItems are only created in the context of an Invoice record (which also means they always relate to one and only one invoice record, and never to none).
To assign a LineItem record to a Process record, we're going to insert a copy of our target Process record's primary key into the foreign key field in the LineItem table. To do this we need to see a list of LineItems that are not yet related to any Process record (that are unassigned). This could be done with a global text field, placed in the Process table, where we type "N" into the global text field, and then delete this field from our layout. We then create a text field in the LineItem table, and set the field up to automatically create "N" for each new record. To make this work, we must create a relationship between globalTextField in the Process table, and the textFieldSetInitiallyToN in the LineItem table, and set the the relationship operator to "equal" (so it's an equijoin). Then, in usage or practice, we set the text field in a LineItem record to Y (while also assigning it to a Process record by inserting the correct Process key copy into the foreign key field in the LineItem record -- which means we are changing or updating two fields in a LineItem record during the act of assignment).
But, what about not wanting null values? For instance, until a LineItem record's foreign key field is filled, its empty. The relational theory frowns on empty attributes. So what if instead of creating a global text field and setting it to "N", one were to create a global number field and set it equal to -1 (negative one, to be explicit and clear). Then also, instead of a text field in the LineItem record that is automatically set to "N" upon the records creation, if one were to use the already-created-foreign-key field but in this case set it up so its set to -1 upon each records creation. Then to have a relationship between global-number-field-set-to-"-1" in the Process table and the-foreign-key-initially-set-to"-1" in the LineItem table.
Now, when we assign a LineItem record to a Process record, the very act of inserting the correct Process primary key into the LineItem record's foreign key field will cause the LineItem to be both removed from the list of unassigned LineItem records, and also assigned to the correct Process record. In this way, the foreign key field in the LineItem would serve a dual function, and there would be no null values in the foreign key field, and only one field in the LineItem record would have to be updated to both assign a LineItem record to a Process record, and remove the same LineItem record from the list of unassigned LineItems.
I'm confused on which path to take, and what I might be failing to see or realize.
(Note, that I've left unsaid how one creates a value list of unassigned LineItems, so hopefully the wise reader can focus on my question, which is confusing enough.)
To add to this: what if use the -1 approach, as the first means of removing a LineItem record from the unassigned LineItems (again, we would remove by changing the value of -1 to a value that is a copy, echo, or clone of the foreign key of the Process record we wish to relate the LineItem to). Then as a second, more distant way (maybe meaning based on yet another table occurrence, maybe not?), we could also remove a LineItem from a list of unassigned LineItem records by changing the textfield"N" to a value not "N", like "R" for rejected, et. This way we could move line items out of the picture, without destroying the original invoice in the process.