Sql-server – Designing a database tables for the following scenario

I am not sure if this is the right place to pose such questions. But here some remarks and questions anyway. They are too long for a comment.

• I don't understand the word representant. I did not find it at http://dict.leo.org/. Dou you mean representative?

• With telephones you mean phone numbers and with emails you mean email adresses?

• Why is telephone a weak entity? What is its identifying entity? For me it is not a weak entity. A phone extension may be a weak entity. The same holds for email and address.

• can a representant have more then one address? This is not excluded by the text but it is excluded by the ER diagtram.

• Why do you name some entities as singular and some as plural? I think a consisten notation will be more clear.

• Why do one need a reading direction? What is the difference if one reads the diagram from bottom to top? I see no difference.

• "if a representant is removed it must be saved in a list" That does not describe any relationships between a removed representant and the remaining entities. "it must be saved" is a term of computer programming and not of the business domain. It is not clear to me what you want but using an attribute to a relation may be not the appropriate solution. From your drawing I conclude a telephone is assigned to a representant. If the representant is removed the removed representant has a telephone too? If yes then this telephone cannot be used by then new representant for this address because a telephone has only one representant assigned.

You need to add another table: ProjectContact that will be an intersection between ProjectClient and ClientContact. This will let you say which contacts apply to a particular project.

The issue that you're going to have is controlling the integrity of the data. You want to make sure that all project contacts come from clients that are actually on the project. This can be done with declarative referential integrity (DRI) or by application logic. Based on your table design, you seem to be pretty firmly in the camp of "every table gets a surrogate key" which makes using the DRI approach a little harder.

To use DRI for this, you want the to use a compound PK for both ProjectContract and ClientContact and in each case it should contain ClientID. The same is true for the new intersection table, ProjectContact. This will ensure that ClientID will be the same and your data must make sense.

Here is an illustration of what I'm suggesting...