Solved

SQL Database Design

Posted on 2008-06-09
3
1,340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

726 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