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?

  • To clarify: an invoice can have 0 or 1 Plants, 0 or 1 GardenRentals, and 0 or 1 Courses (and must have at least one of these) - but could never have 2 of any single item on it? I ask because this is quite atypical; invoices usually are set up with multiple line items, each consisting of 1 or more of one specific item.... – RDFozz Apr 25 '17 at 16:59
  • 2
    There is a tag, subtypes that collects questions similar to yours. You might want to add that tag to your question and look lover the questions that are already under that tag. – Walter Mitty Apr 25 '17 at 19:06
  • Subtypes is part of the EER system, and this assignment is explicitly based around the ER system. – root Apr 26 '17 at 2:52
  • What about this guys. If I make the invoice entity have foreign keys to Plant, Course, and GardenRentals, and an invoice is made for a Plant, could I simply put the value of the other foreign keys as 0? (Since I'm not being charged for any Courses or GardenRentals in this particular instance, only the plant). 0 /= null, which would therefore resolve this problem. – root Apr 26 '17 at 3:02
  • @RDFozz I read the question as: "an Invoice is made regarding 1 "Sale" where Sale can be either a Plant, a Course or a GardenRental". – ypercubeᵀᴹ Apr 26 '17 at 8:56

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:

Invoice and Type Model

I hope this answer will help you.

  • 1
    This helps a bit, but it doesn't answer my question regarding my understanding of how foreign keys actually work. Also, in the main Invoice entity, say the implemented type is Plant. The plant invoice would then have PlantID and Plant Name, among other things. How would you design the invoice entity so that it can cover not only Plant, but also courses, and also garden rentals? (Remembering that each of these different invoices would have different applicable attributes). Eg, PlantName is not relevant to an invoice related to Courses. – root Apr 25 '17 at 13:26

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.

  • This looks like a great answer to my question. The only thing left is that I'm unsure how this would be modelled in an ER diagram. But thanks very much for this :) – root Apr 30 '17 at 5:47

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.