[thelist] Database Design help

Carolyn Jewel Carolyn.Jewel at LEGACYNET.COM
Mon Apr 4 10:47:22 CDT 2005


 
Hi there. I see some issues with your design that you might want to
consider. First, the Restaurant table is denormalized and this can very
quickly give you data problems. (Note that you mention your client is
considering adding more restaurants and locations). Typically, it's
better to place addresses in a separate table and then create a middle
table joining Restaurant and Address. Likewise, placing phone numbers in
a separate table would allow you to assign more than one number (phone
and fax, for example) via a middle table such as RestaurantPhone,
without you having to continue to duplicating data you've already
entered. 

Consider the scenarios below and imagine what happens to your Restaurant
table when you try to accommodate these:

A restaurant has one number for reservations, another for orders and a
fax number for faxing orders
A restaurant has multiple locations in the same city
A restaurant has locations in more than one city (actually, you already
have this situation)

Suppose further that a restaurant has all these conditions. Your current
structure requires you to make many, many more records in the Restaurant
table, and that's n opportunities to make a typo in all your duplicated
fields. This is a design that I have seen, time and again, lead to
garbage data and abandonment of the database because the data can no
longer be trusted. Unfortunately, this tends to happen very quickly.

It doesn't seem to me that there's a clear relationship defined between
Restaurant, CuisineType, ServiceType, City and color code. CuisineType
and ServiceType should be lookup tables and their primary keys should be
foreign keys in the appropriate main or middle table(s).

Why do you need a city table? The data already exists in the address
data. If you really do need a City table, then that PK should be a FK in
table that holds address data (rather than a varChar City field in
Address data), otherwise, you risk orphaning data where you've
misspelled whatever field you're using in joins that select or sort by
city. 

It looks to me like you're putting city all over the place. I think I'm
confused about what you're trying to accomplish.

Maybe it makes more sense to have a ColorCode table whose PK resides in
at least your ServiceType table and possibly the address table.

I think, though, to digress a bit, that once you expand to 5 or 6
cities, you will create pages that do not have a unified look and feel.
You're asking your users to remember which color represents which city.
I'm not convinced that color coding the cities is actually all that
helpful. And, you have not taken color blindness into account.

Carolyn J.


-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Kath
Sent: Sunday April 03, 2005 9:20 AM
To: thelist at lists.evolt.org
Subject: [thelist] Database Design help

If I recall correctly, we have some database gurus on the list.  I'm
hoping that you can help with a design dilemma I have.

I'm a database newbie attempting to design a database that will hold the
restaurant info on this site - 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



****This electronic mail message, and any attachments transmitted with it, contains confidential information, intended only for the named addressee(s). If you are not the intended recipient, or a person responsible for delivering this e-mail to the intended recipient, you are hereby notified that the use, distribution, copying, or disclosure of this communication is strictly prohibited. If you have received this e-mail in error, please immediately notify the sender by reply e-mail, and delete all copies of this communication from your computer and network. Thank you.*****



More information about the thelist mailing list