Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1349
  • Last Modified:

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
0
computerstreber
Asked:
computerstreber
1 Solution
 
Guy Hengel [angelIII / a3]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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now