Solved

SQL Database Design

Posted on 2008-06-09
3
1,338 Views
Last Modified: 2011-10-19
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
0
Comment
Question by:computerstreber
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21745232
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21745234
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
 
LVL 22

Expert Comment

by:dportas
ID: 21745546
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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article will show, step by step, how to integrate R code into a R Sweave document
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question