I have two questions:
1.) I have a table called chefs
CREATE TABLE #Chefs (
ChefID IDENTITY(1,1) PRIMARY KEY
, First_Name VARCHAR(50)
, Last_Name VARCHAR(50)
This table list the names of all the chefs. Now, I have another table called restaurants.
CREATE TABLE #Restaurant (
RestaurantID IDENTITY(1,1) PRIMARY KEY
, Restaruant_Name VARCHAR(50)
Here is my problem....
Each restaurant can have two chefs, one is primary and the other is secondary. How should I implement this in the design? Should I had a Primary_Chef and Secondary_Chef to the Restaurant table and place a foreign key on both? Or should I implement a many to many relationship (example in attached ERD?
If I implement the Primary and Secondary Columns, how would I write a query that extracted the names for both the second and primary chefs?