Well, the truth is I am trying to get my head around Entity Framework, so I need a database designed the way it "is done", as opposed to, as would say I guess, the way it makes sense :).

I thought I had _the_ application with quintescencial simplicity, and here I am faced with a "complex" relation I doubt I'll be able put down EF's throat.

Ah well. It takes time for an old dog (fox?) to change his ways.


>>Am I the last man standing, faced with writing stockmanagement systems then? ;)
>
>Yes :).
>
>Kidding aside, your situation is unique in having transactions which create stock items. Which should not be so unique, as in any stock management some new items appear from time to time, but I guess everyone has written some way to open up the item form, insert this new item and then return to the transaction. But they don't have the creating transaction, then... Which you still may do this way - pull up a popup form to create the item, return its PK, insert it into the current transaction record, then on save check whether the item record has a CreatorTransaction FK, and if not, write the transaction's PK into it.
>
>But, knowing what you're dealing with, the information in the transaction itself is probably all the info about the item that you have at the moment - the rest of it is Schrödinger's cat, unknown until the parcel arrives and is opened. So... up to you.
>
>If you're doing this from Fox, if you give up on automatic keys, you could create both keys in advance, prepare both records with PKs and FKs and send them - all in the same transaction. Then you wouldn't need null FKs at all.
>
>>Many thanks for your thoughts.
>>
>>
>>
>>
>>>>Assuming a StockItems Table and a Transactions Table. If I want the Transactions Table to have a Foreign Key into the StockItems, and the StockItems to have a foreign keys into Transactions, there's no way I will be able to add a record in both tables (some Transactions will add StockItems) during the same transaction if I make both Foreign Keys non nullable.
>>>>
>>>>a) Is there?
>>>>b) Is it ok the set foreign keys nullable?
>>>>c) Is it not best practice to cross reference like this?
>>>>d) is there anything else wrong in my reasoning?
>>>
>>>Actually I think you should be able to automatically create a parcel from an insert trigger when you have a transaction which creates it (i.e. purchase). Not that I've tried it, but somehow sounds possible :).
>>>
>>>There'd be an awkward dance, when you're creating two records with automatic keys, which should need to be created in the same transaction, then PK of each inserted as a FK into the other.
>>>
>>>Other than that, if you intend to create these records independently, you'll have to write even more code and would have to wrap it all in a transaction, or else you may get dragged into transactions which created new parcel which doesn't exist, or have parcels created from nonexistent transactions - which will be returned as an error because your constraints prevent that.
>>>
>>>If that doesn't work, you may have a record with -1 as a PK in both tables, and use that as a temporary FK so at least you don't get clobbered by your RI rules. Or have it your way and allow nulls... You are aware that you're talking to perhaps few dozen people and the chances that anyone has actually tried what you ask are small, no?