Re: Need help with normalizing

"Seth Brewer" <fieury_at_hotmail.com> wrote in news:dZEy9.51971$ar4.117434_at_sccrnsc03:

> I'm still new to this and my question may seem dopey, but here goes:
>
> I'm building a menu database which eventualy will allow people to
> order a meal by date. Only certain meals will be available on certain
> dates, but certain meals may be available on many dates. To make
> things worse I want to have the main courses and sides in separate
> tables so that they can be mixed and matched many-to-many.
>
> What I've come up with so far is:
>
> MAINCOURSES
> mainCourseID
> title
> description
>
> SIDES
> sideID
> title
> description
>
> MEALS
> mainCourseID
> sideID
>
> Where I hit a wall is date. If I make it a field in MEALS I will have
> redundant dates if a meal has more than one side. This could be a
> problem if I want to make certain main course/side relationships
> permanent. If I make it a separate join table with mainCourseID and
> date I'll run into trouble if I ever want to select a meal by date
> (select * from mealdate where date = somedate inner join meals on
> mainCourseID =... will give me all meals).

It seems that you're looking for a structure like this:

Meals
mealID (PK)
mainCourseID (FK)
date

MealSides
mealID (PK, FK)
sideID (PK, FK)

Alternatively, you could try the following: Meals
mainCourseID (PK, FK)
date (PK)

MealSides
mainCourseID (PK, FK)
date (PK, FK)
sideID (PK, FK)

That way, you can have a main course only once per date and have one set of sides for this main course.

hth,
Heinz Received on Fri Nov 08 2002 - 08:51:57 CET