I have a Master/Detail relationship. My business rules state that there must be at least one Detail Item for each Master Item. From experience I know that 99% of the time that there actually is only One Detail Item for each Master. It is very rare that a Master Item will have more then one Detail Item.
There is no such word as "rare" in Relational Database Design. This relationship is either One-to-Many or it isn't. Which is it?
Since I know that I always have to have one Detail Item for each Master and that almost all of the time that there is only one Detail item, would it be a good or bad idea if the Master table contained the fields FieldA and FieldB?
That would be a Bad idea. Very, very Bad. That would in fact be "Not Relational At All".
This way I would only have to concern myself with the Detail table on rare occassions?
This is exactly wrong. Your code will, in fact, have to concern itself with the Detail table every single time because until it checks, it does not know
how many Detail records there are.
It is much more easy, effective and efficient to just always assume that there could be multiple Detail records, instead of writing special checks and special-case code to try to avoid this possibility. This is one of the essential truths of the Relational approach.
Bite the bullet and do it right.
, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."