Kjell Rilbe wrote:

> My immediate thought is that you should stick with null, but I'm sure
> others have more insightful comments to make. I just think that as long
> as you use null for what it actually means "unknown value", then you're
> OK. In that case null in your case would mean "I don't know which master
> record this detail record belongs to". If, on the other hand, your
> business logic is more like "I know this detail record doesn't belong to
> any master recpord and that is OK", then perhaps your approach is more
> "to the point".

NULL is in my opinion not 'unknown', but non-existent value. So IMHO
when there is no target record for the foreign key it should be NULL,
not some randomly made up 'system' record. It is ugly and usually
unnecessary.

There is no real excuse for using a System record, except maybe if your
business rules require some kind of 'orphanage'-record to collect
orphaned records for further processing or checking. Even then you can
also argue that the orphanage can be represented by NULL, implementation
wise it might be easier to have such an orphanage record.

Mark
--
Mark Rotteveel