Re: Multiple Foreign keys from 1 child to 1 parent discussion

[Philip Curtis]

Peter, Thank you for the comments on the use of multiple foreign keys. In this case they would point to potentially different users. So you have helped me with this!

To clear up point two:
The bill table is a child of encounter. You cannot have a bill table entry without an encounter. However, because of the use of foreign keys, it appears to be a child of user. You cannot must have encounter to have a bill so the logic should flow through encounter. However, normalization has not been observed for at least the user_id field as truthfully it should be reference through encounter and not directly to user.

I have other examples in the schema where parent ids have become different if traced through different paths of foreign keys. A really dangerous problem that has created bugs but so far no disasters.

I am looking for a best practice. Creating foreign keys when there is no real relationship between the tables seems to me to be a waste of resources and introduces confusion. Additionally, I am concerned about the cost of multiple checks of foreign keys to one single key table.

Point three: do the biz rules allow referencing through different paths (I'm summarizing your comment, please excuse if I didn't get it correct) Well, the business rules are not that well formalized, I'm fairly certain this is left to the developer to do whatever is easiest.

I think that I am starting to fill in some of my own answers on this.

Thank you for your thoughts, I do appreciate them.