Database Design – Bad Design Points in Entity-Relationship Diagram

database-designdatabase-diagramserd

The objective is to design an Entity-Relationship Diagram for a company that has addresses, telephones, emails and representants. Here's the list of the constraints:

a company has one or more telephones
a company has one or more e-mails
a company has one or more address
an address has one or more representants
a representant has one or more telephones
a representant has one or more e-mails
if a representant is removed, then it must be saved in a list "last
representants".

Other constraints are (hopefully) represented in the diagram, using the following notation:

circle: attribute
double-lined rectangle: weak entities
double-line: total participation
line ending with an arrow: cardinality 1:N (arrow points to the "1")

Entity Relationship Diagram

Entity-Relationship Diagram

I designed that diagram. I'm sure there are some bad design points, because I'm a beginner, and I would like to hear your opinions, suggestions and criticisms.

More information

(based on the answer by miracle173)

English is not my native language, I should've told that in my question, so please excuse my poor word picking. By representant I mean a person that will speak on behalf of the company. With telephones I mean one or more phone numbers. With emails I mean one or more email addresses.

Here is why I chose telephone and email as being weak entities: when a company is removed, I want the list of telephones numbers and the list of emails addresses associated with that company to be also removed. Did I achieve that goal with the use of weak entity?

An address of a company can have more than one representant, but the representant can only work for one company at a time (hence the restriction in my diagram). I agree, some singular and some plural is bad practice. I will fix that.

Changing the relation to more precise verb, like "represented by" (instead of "an address has representants"): from top to bottom, one can read "An address is represented by representants"; from bottom to top, it gives incorrect reading ("A representant is represented by a company"), when it should be "A representant represents a company". So I think it makes difference the reading direction.

What is wanted with "it must be saved" is: to have a short list, like a small history, of the last representants that worked for the company. Example: If John – which is a representant of Miracle's Corporation – suddenly is fired from the company, he should be removed from the Representants list (along with his phone numbers and email addresses), but it is desired to keep him in that short history list after his deletion from Representants.

Best Answer

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.

Related Solutions

Drawing an entity-relationship diagram for a movie sales business domain

I would use (title,prod_date) as the primary key, since movies are not uniquely identified by their title alone (remakes, for example). In my opinion, the first diagram is preferable, the sub-attribute approach in the second diagram seems a bit convoluted to me.

Database Design – Converting ERD to Table Effectively

Looking at Relationship 1 as an example. The primary key of this table is ID_PhoneNumber and ID_Company. The CompanyName, however depends only on ID_Company alone. The table is not in second normal form. For the design to be normalised it would have to be:

Company (ID_Company, CompanyName)
Relationship1 (ID_Company, ID_PhoneNumber)

The same argument holds for Relationship 2 and 3.

Telephone has only two columns - the ID and the number. I'd suggest quite strongly that it would be an error to have two rows in this table with the same number. So a unique contraint on the number would be appropriate. The ID column, which is the primary key, will have a unique constraint, too. So we have a table where each row consists of a unique number (ID) paired with a unique number (the phone number). It seems to me one of these is redundant. You may choose to retain the ID as a surrogate key since an integer (the most likely data type) will be shorter than a full international telephone number, especially with formatting characters embedded. Adopting this would give the table

Relationship1(ID_Company, PhoneNumber)

I would be inclined to use call it CompanyPhone at this point.

I know I'm mixing logical and physical arguments. I'm assuming your intention is to implement a system and not simply derive an abstract model.

One thing I haven't covered, and isn't mentioned in your question, is whether a phone number can relate to a company or an employee or a supplier i.e. are the relationships mutually exclusive.