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