Xrhrft

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:

• 
  Plants
  
  
  
  
  • PlantID
  
  
  • PlantName
  
  
  • PlantPrice
  
  
  
  


• 
  Courses
  
  
  
  
  • CourseID
  
  
  • CourseCost
  
  
  • StaffID
  
  
  • CustomerID
  
  
  
  


• 
  GardenRentals
  
  
  
  
  • GardenID
  
  
  • CustomerID
  
  
  • GardenLocation
  
  
  • GardenType
  
  
  • MonthlyRentalFee
  
  
  
  

My question is, can I have a main Invoice entity type, that uses the details from the other entity types (that is, Plants, Courses, and 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?

This is based on my understanding, it may change based on your exact requirement.

• 
  A invoice must belongs to at least one invoice type.


• 
  A invoice type may have more than one invoices.

Invoice Type:

• Plants


• Courses


• GardenRentals

For the above mentioned requirements, the ER diagram, would be:

I hope this answer will help you.

I've answered a similar question here. What you want is something like a Products table containing data common to all invoiceable products (Plant, Course, Garden) with another subsidiary table for each product providing the additional data unique to each different product.

Given the ID value of a product, complete product information would be gathered with the sub table according to the type value found in Products. In fact, it would be very useful to create three views (conveniently named Plants, Courses and Gardens) that would show all the information for each type of product.

create table Products(

  ID    int  auto_generated primary key,

  Type  char( 1 ) check( Type in( 'P', 'C', 'G' ),

  Price currency;

  constraint UQ_Product_ID_Type unique( ID, Type )

);

If ID is unique on its own, why create a unique constraint with the combination (ID, Type)? So the aggregate field can be the target of a foreign key reference.

The Invoice table would be something like this:

create table Invoices(

  ID    int  auto_generated primary key,

  ProdID int not null,

  ProdType char( 1 ) not null;

  ...

  constraint FK_Invoice_Product foreign key( ProdID, ProdType )

    references Products( ID, Type )

);

This design makes it impossible, for example, to create an invoice for a Garden if the ID in Products is designated as a Course or Plant.

The linked question and answer should provide the details you need. If not, a couple of my answers to related questions can be found here and here.