Is it common to have multiple relationships between two tables?
It is not uncommon to have multiple one-to-many relationships between two tables. You have correctly modeled the case you have described. However, I generally separate created-by and owners relations. This permits someone other than the owner to enter proposals and tasks.
How to tell Entity Framework which properties are involved in one Association?
You have to tell Entity Framework which properties in both entities are involved in one association. In fluent mapping API this is: You have to use the fluent API because by default, EF will try to create two foreign keys with cascaded delete. SQL Server won’t allow that because of its infamous “multiple cascade paths” restriction.
Do you have many to many relationship between employees and proposals?
You do have a many-to-many relationship between employees and proposals. This resolved by the tasks table. Having multiple fields that reference another table doesn’t make it a many-to-many relationship. You simply have multiple one-to-many relationships between the tables.
When to use data annotations in Entity Framework?
You can use the Data Annotations to configure how navigation properties on the dependent and principal entities pair up. This is typically done when there is more than one pair of navigation properties between two entity types.
Can a database have more than one foreign key?
SQL databases don’t have the ability to manage a single foreign key column being for one of multiple parent tables. One way that people sometimes deal with this issue is to use a single pool of IDs so that there is no confusion for any given ID whether it belongs to one sub-type or another.
What are the problems with a relational database?
Relational databases are not built to handle this situation perfectly. You have to decide what is most important to you and then make your trade-offs. You have several goals: Maintain the constraint that each account belongs to either a corporation or a natural person. The problem is that some of these goals compete with one another.