Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Consider the following ERD:

ERD

Suppose that all entities only have two attributes: a PK plus a name/number.


I want to convert that ERD to tables.

  • Is it needed 4 tables (1 for each relationship plus the phone table)? Is there a better solution?

Here is my solution (4 tables, one for each relationship plus the phone table):

Relationship 1 (R1)
-----------------------------------------
ID_PhoneNumber | ID_Company | CompanyName
-----------------------------------------

Relationship 2 (R2)
-------------------------------------------
ID_PhoneNumber | ID_Employee | EmployeeName
-------------------------------------------

Relationship 3 (R3)
-------------------------------------------
ID_PhoneNumber | ID_Supplier | SupplierName
-------------------------------------------

Telephone
----------------------------
ID_PhoneNumber | PhoneNumber
----------------------------

I assumed that is not needed to add three more tables, one for each entity (Company, Employee, Supplier) since those entities are already represented by the tables of each relationship (R1, R2, R3).

I'm not sure if that decision is correct or if I'm making good use of the fundamental concepts (maybe I'm lacking some).

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Yes, a phone number can be shared by Company and Employee. I created a fictitious scenario aiming to ease the answering of the question, but now I realize that it was a mistake. Instead of Employee and Supplier, I actually have Branch (Branch office) and Representative (of a Company and of a Branch). So, in that latter scenario, the business rules do allow for Company and Representative to have the same phone number: that happens when the Company "is" the Representative (when the Company is the smallest possible: when it has only one employee which is the owner itself). –  feelthhis May 1 at 22:25
    
And yes, I will implement a system. And I want to learn the most I can in the process, and you're helping me very much with that :) –  feelthhis May 1 at 22:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.