So I was doing some research on DBA Stack Exchange for my Database Design university assignment, and I came across the question entitled "3rd Normal Form violation?", which is quite similar to my own issue, but doesn't inherently answer my question.
I have three different objects and their attributes that can have
Invoices generated for them:
My question is, can I have a main
Invoice entity type, that uses the details from the other entity types (that is,
GardenRentals) through foreign keys? (Remembering that an
Invoice may only be about one of those three things, or multiple things). The reason why I ask is because if I'm making an
Invoice for a
Plant purchase, then obviously that particular
Invoice is not using the data from the
Course entity type, nor the
GardenRentals entity type, which would therefore make the foreign key in those two attributes NULL (assuming I understand correctly).
Having those NULLs there would therefore violate first normal form (1NF).
But the so called "fix" for this would be to have three different invoice entity types, covering each of the entity types that "could" have an
Invoice generated for them. To me, that just doesn't seem right, nor efficient in the slightest (that being said, performance is not a factor here, due to the fact that it's a purely theoretical assignment).
Also, is my understanding correct that if a foreign key is used as an attribute for an entity type, then the corresponding entry becomes a substitute for data used? (Meaning that if the relationship is NOT always used, the entry would therefore be NULL). The reasoning behind this thinking is that each entity type can be represented by a table, with each attribute represented by a column, and each new entry being a row in that table. Is this understanding flawed?