MySQL ERD Implementation – Ensuring 100% Compliance

erdforeign keyMySQLsource codetable

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

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?

Best Answer

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.

Related Solutions

Mysql – Implementing something similar to “pointer arrays” with MySQL

You are conceptualizing this from something other than a relational database perspective, but it sounds like what you're looking for is a junction table (which also has several other names, all describing pretty much the same construct).

This structure allows multiple "this" to reference multiple "that" with unlimited flexibility with regard to which or how many of "this" references which or how many of "that"... you create a third table that is conceptually "between" the existing "this" and "that" tables. The juction/map/link table often has just two columns, "this_id" and "that_id" which combine to make up its primary key... and you create the "pointers" by inserting rows in that third table containing each this_id/that_id pair that reflects the way the records should be related.

You typically then would retrieve the records with a join.

Database Design – Converting ERD to Table Effectively

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.

Question

Best Answer

Related Solutions

Mysql – Implementing something similar to “pointer arrays” with MySQL

Database Design – Converting ERD to Table Effectively

Related Question