I have two tables inspections and tasks. The inspection is allowed to have a single task but not many.

Inspections don't need tasks and really there is no direct relationship. However, I have a link in a grid that goes to the optional task. Also, there is really no relationship to an inspection for the task so it would seem wrong to have a InspectionId field. Also, you can see that this would start to get ugly because I have four other types that are related in the same way. Having a whole bunch of unused foreign keys on the task would be nonsense.

I can easily implement a TaskId on the context tables and forget about it but it seems wrong.

Lastly, I can add a join table and use a many to many but this sort of adds some overhead and complexity when querying. It seems there is no good answer. Deep down I really want to make all my relationships many to many and relate all my entities to join tables. It seems to simplify many challenges save for for querying data of course. That part of me wants to create views with pseudo foreign keys mapped through the view for querying purposes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.