Implementing a simple ERD in MySQL: is the implementation below 100% compliant with the ERD?

Question

ERD

Consider the following ERD:

---

Tables

From it, I derived the following tables:

Company
------------------
c_id        c_name
------------------

CompanyEmail
------------------
c_id        e_id
------------------

Email
------------------
e_id        e_addr
------------------

Note: the use of the relationship table (CompanyEmail) is not arbitrary.

---

Implementation

From those tables, I made the following implementation (tested in MySQL Workbench 6.1):

• http://pastebin.com/3A0H21kw (please bear with my lack of skills :)

I guess the most important part of it (besides learning the language) was deciding which attribute is FK and the use of CASCADE. Is it [CASCADE] the implementation of parcial/strong participation? and/or weak/strong entity?

---

Question

• Does my implementation accurately complies with the ERD? If not, what would be a better solution?

Answer 1

For the simple case shown, with a business rule something like "Each company can be contacted at zero to many email addresses; each email address directs to exactly one company" it would be more usual to have the company id as a foreign key in the email table. What you have shown will work, but there will be a one-to-one relationship between Email and CompanyEmail, which is redundant.

For your linked question, where you actually have a Company, a Branch and an Employee, all of which can have many email addresses and any one email address could relate to any or all of Company, Branch and Employee, things are slightly different. In the logical data model I would be inclined to show Email and the intersection entity types explicitly to document the rules fully. In the physical table design, however, I would consider omiting the Email table. This is because its only columns are e_addr and e_id, so e_id is really just a synonym for e_addr and e_addr can replace e_id in the intersection tables CustomerEmail, BranchEmail and EmployeeEmail. You can still answer all the questions about one email address's re-use but won't have the actual table to maintain. It would mean an email address disappeared from your system when it stopped being used. That's probably OK in practice.