# Conversion of simple ERD to Table: is the solution below any good?

Consider the following 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).

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.

• 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). May 1, 2014 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 :) May 1, 2014 at 22:25