0

I'm working on an ASP.NET MVC 4 application where at each 'level' there is an option to add a fee. Say I have a Company which can have many Campaigns (1-to-Many), and Campaigns that have many Products(1-to-Many). [Using DB first design]

A Company, Campaign and Product can all have a list of 'fees' associated with them, and the data for the fee is the same [name, value, FK, etc.]

I'm currently working with a "CompanyFee", "CampaignFee", and "ProductFee" table that have a Foreign Key for it's associated Table. [Identical tables other than the FK relationship]

Does it make sense to just have a "Fee" table that has a Foreign Key Column for each Table that can have fees. So "FeeTable" would be [name, value, CompanyID(FK), CampaignID(FK), ProductID(FK), etc]

This would mean that it would be considered a "ProductFee" if the ProductID is the only FK with a value, while the others would be null.

Is it best practice to keep these table separate? What are other implications of either structure?

user3250366
  • 3
  • 1
  • Welcome to the world of *polymorphic associations*. Try to get Bill Karwin's book "SQL Antipatterns" and read his recommendations. Or this: http://stackoverflow.com/a/2003042/861716. Or this: http://stackoverflow.com/q/7000283/861716 – Gert Arnold Jan 29 '14 at 22:02

1 Answers1

1

This discussion is only concerned with data modeling and not EF or ASP.NET MVC considerations. I see 2 options. Both are correct. Option 1 requires that the PK for fee be a FeeID and that CompanyID_FK be not null whereas the other 2 FK should be nullable.

If you want to make your model flexible, I suggest you go with option 2.

However, if you don't plan to extend the model or if you have no further requirements in this area, option 1 is a valid choice and it uses less number of tables and will require less lines of code.

The problem with option 1 is that If in the future you create a table related to the fee table, you will get into odd situations for example:

A. If you need to have a "fee description" table you will have to have 1 table for all 3 fees. This is not good because some descriptions may not apply to all fee types.

B. If you need to have an "fee approver" table, again, you will have to have 1 table for approvers of all 3 fee types.

You won't face the above issues with option 2.

The obvious problem with option 2 is the extra number of tables and the extra lines of code.

Although you have not provided size information, I don't see size to cause a performance issue based on the nature of the domain.

enter image description here

NoChance
  • 5,632
  • 4
  • 31
  • 45
  • I ended up going with Option1 and has been working fairly well sense. Thanks for the help (and sorry for the much delayed response) – user3250366 Mar 10 '15 at 19:35