SQL Database Design

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?

erd.bmp
LVL 3
computerstreberAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
the choice is basically yours, this is a case of "discussion" of how far you want to push normalization ...
I think, if you can confirm that there will be NEVER more than 2 chefs to be stored in there (or of interest), go with the 2 fields.
and yes, both fields would be a foreign key.


>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?
you have to join 2 times:



select * 
from #restaurant r
left join #Chefs c1
  on c1.ChefID = r.PrimaryChefID
left join #Chefs c2
  on c2.ChefID = r.SecondaryChefID

Open in new window

0
 
chapmandewCommented:
I would implement a many-to-many intermediate table between the two...

chefxref(xfefid identity(1,1), primarychefid, secondarychefid)

add the xrefid to the restraurantcheftable
0
 
dportasCommented:
I think you have some other problems. Firstly, why do you allow restaurants with nullable names?

Secondly, what identifies an individual restaurant? Your model allows duplicate names with different surrogate keys. For example:

RestaurantID Restaruant_Name
------------ ---------------
1            Carluccio's
2            Carluccio's

I don't know your data but that looks meaningless to me. How is the user supposed to identify Carluccio's in Cambridge from Carluccio's in Notting Hill, London? The user won't know if they are really two restaurants or just one that was entered twice in error.

So if you really require only a name and nothing else then it's better to make the name into a key as well:

CREATE TABLE Restaurant (
 RestaurantID SMALLINT IDENTITY(1,1) PRIMARY KEY,
 Restaruant_Name VARCHAR(50) NOT NULL UNIQUE);

Now at least you can avoid some meaningless duplication - although you may want to add something like an address as well.

Similar remarks could be applied to your Chef's table. In the expectation that you'll need to support restaurants with duplicate names and probably chefs serving multiple restaurants I would probably go with something like the model in your ER diagram (but with additional keys and without the nulls).

The rule of thumb is that when IDENTITY is used as a key it should not be the ONLY key of the table.



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.