CODEWITHSUNDEEP
databaseentity-frameworkdatabase-design
user3250366
user3250366

Reputation: 3

Multiple tables with the same schema or 1 table with multiple Foreign Keys?

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?

Upvotes: 0

Views: 752

Answers (1)

NoChance
NoChance

Reputation: 5772

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

Upvotes: 1

Related Questions