Solved

SQL Database Design

Posted on 2008-06-09
3
1,335 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now