1

ERD

Consider the following ERD: enter image description here


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):

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?
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.

| improve this answer | |
  • "In the logical data model I would be inclined to show Email and the intersection entity types explicitly to document the rules fully." Do you mean like this? About the table Email. More specifically, about the question "make the e-mails existence-dependent or not?" ((a) an e-mail does or (b) does not exist by itself?): I didn't know what to decide. In the current implementation, an e-mail doesn't exist by itself (b); but, due to a mistake in the current implementation (I guess), I changed to the other way around (an e-mail can exist by itself (a)). – feelthhis May 13 '14 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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