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).
Best Answer
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:
The same argument holds for Relationship 2 and 3.
Telephonehas 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 tableI 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.