[thelist] Database Design help

[john at johnallsopp.co.uk]

>  http://www.gemsguides.com/

> So far I have 4 tables:
> Restaurants with RestID (key) RestName, RestAddress, City, RestPhone
> and
> CuisineType fields
> Cities with CityID, CityName, CityAbrev, ColorCode fields
> CuisineType with CuisineID and cuisineType
> Service Key with ServiceID, City (because of the color coding),
> ServiceType
> and iconURL

> I'm stuck on how to get the services tied into the Restaurants

I'm not one of the database experts, but here's my take on it and
we'll see if I'm close to right :-)

Because Restaurants and Services have a many to many relationship .. a
restaurant can offer many services, and a service may be available
from many restaurants, you need a new table (call it availability
perhaps). It has two fields, RestID and ServiceID. The combination of
both is unique, and forms the primary key. So, that table contains,
for instance, Rest1 Service1, Rest1 Service3, Rest2 Service1, Rest3
Service8, anon.

Then you connect the whole lot together with your SQL, so family
friendly restaurants are Select * from restaurant, service,
availability where restaurant.RestID=availability.RestID and
availability.serviceID=service.serviceId and
service.serviceType="family friendly";

I don't think you need the CityID in the Service table (because you'll
get to it through the restaurant table.

You might want to look up database normalisation for the principles
behind this.

HTH
J