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?