MySQL Workbench – question about creating FK relationships

MySQLmysql-workbench

Forgive me a little new at databases and I came across something while working with Workbench that I have a question on.

In a very simple testing database, with three tables: Company, Department, Employee

Company          Department      Employee
 - id (PK)(AI)   - id (PK)(AI)    - id (PK)(AI)
 - Name          - Name           - Name

I created a 1 to many relationship between Company and Department and since Employees can only have one Department and One company, I created a 1 to many relationship between Company and Employee and Department and Employee.

When I did this, Workbench created a third Relationship column in Employee Called Company_Department_id and all of my columns that have FK all have now been check as PK as well, tables now looks like this:

    Company       Department             Employee
    - id (PK)(AI) - id (PK)(AI)           - id (PK)(AI)
    - Name        - Name                  - Name
                  - Company_id (PK)(FK)   - Company_id (PK)(FK)
                                          - Department_id (PK)(FK)
                                          - Company_Department_id (PK)(FK)

Questions

Why does workbench automatically create the Company_Department_id table? I feel like I should be able to delete it, but I just want to make sure I'm not missing something
How come Workbench automatically tagged each related column as a primary key? Is it because they are primary keys in their own tables? Should I keep them as primary keys?

Best Answer

It looks like you've used the option to create an Identifying relationship (this is the one with the unbroken line in the toolbox).

An Identifying relationship assumes that the primary key in the referenced table should be part of primary key of the referencing table.

Identifying relationship

Because Company_id is now part of Department's Primary Key, it must also be part of the foreign key of Employee, hence your problem #1.

What you're probably wanting to do is create a Non-identifying relationship. This can be done by either selecting the dashed line from the toolbox, or unticking "Identifying relationship" in the Foreign Key tab of the Relationship properties.

A Non-identifying relationship is a classic foreign key constraint, and simply ensures that any value in the referencing table exists in the referenced table.

Non-identifying relationship

Technically this should resolve your problems, but there are still potential issues with the underlying database design.

For example, there's nothing to stop multiple companies existing with the same name, or multiple departments with the same name, even within the same company.

This could be solved by adding unique key constraints, but another way to tackle the same problem would be to use the Company and Department names as primary keys.

If you were doing this, you'd actually want to use an Identifying relationship, so that the Company name becomes an integral part of the Department.

(You may wish to read up on database theory and database normalisation, as it will help you avoid a lot of traps that you're likely to come up against when building a database)

Another Identifying Relationship

I'm not suggesting that this is a better database design, simply that this is one where an Identifying relationship is valid / useful.

Related Solutions

Sql-server – Why aren’t primary key / foreign key matches used for joins

In many cases, there are more than one way to join two tables; See the other answers for lots of examples. Of course, one could say that it would be an error to use the 'automatic join' in those cases. Then only a handfull of simple cases where it can be used would be left.

However, there is a severe drawback! Queries that are correct today, might become an error tomorrow just by adding a second FK to the same table!

Let me say that again: by adding columns, queries that do not use those columns could turn from 'correct' into 'error'!

That is such a maintenance nightmare, that any sane style guide would prohibit to use this feature. Most already prohibit select * for the same reason!

All this would be acceptable, if performance would be enhanced. However, that's not the case.

Summarizing, this feature could be used in only a limited set of simple cases, does not increase performance, and most style guides would prohibit its usage anyway.

Therefor it is not supprising that most database vendors choose to spend their time on more important things.

Mysql – How to order query results by tag weight in IN(‘tag1’, ‘tag2’, ‘tag3’)

I did

select * 
from posts p
join tag_map tm on tm.post_id = p.post_id
join tags t on tm.tag_id = t.tag_id
join 
  (select post_id, count(tag_id) as tag_count
   from tag_map
   group by post_id) counts
  on counts.post_id = p.post_id
where t.tag in('tag1', 'tag2', 'tag3')
order by tag_count desc, tag asc

and it seems to work OK according to your specs:

Order by tag count - I joined with a sub-select that retrieves the tag count per post_id and ordered primarily by that count (descending)
Order by tag name - after ordering by tagcount/post, the query orders by tag name

Here's the SQLFiddle if you want to experiment more: http://sqlfiddle.com/#!2/8fcd7/9

Update: version of the SQLFiddle with index on (tag_map.post_id): http://sqlfiddle.com/#!2/02b5e/1

PS: this is my first MySQL query, so if there are any criticisms regarding performance/style/anything else, I'd be glad to hear them and learn from them.

Best Answer

Related Solutions

Sql-server – Why aren’t primary key / foreign key matches used for joins

Mysql – How to order query results by tag weight in IN(‘tag1’, ‘tag2’, ‘tag3’)

Related Question