Design Challenge - interesting problem - F1 please 2

[Deadline]

Dear friend,

Here is some 'food' for your appetite for solving problems.

I want a design for the following problem..

*Our client has several cafeterias; let us call them locations.

*They have several stations inside each cafeteria - for example, GRILL,SOUP,SALAD,HOT FOOD,DELI SANDWICH etc..

*Each station serves a several food items - for example, Soup station serving - Chicken Soup, Mushroom Soup, Tomato Soup etc.. and so on.

Now the Menu CoOrdinator, will decide what will be served for each week.. I mean, they do plan for a week in advance. And they post in the company Intranet

Right now, they are modifying the HTML file directly.

We are prosposing an ASP based solution.

The key issue is the database design and the user interface design..

Once this system is in place, the MenuCoordinator should be able to enter the Schedule well in advance - theoretically, atleast 1 year in advance..

I want your suggestions for the database design. And I need suggestions for a good UI that enables the MenuCoOrdinator to fix the food, ie, When, Which Cafeteria, What Station,What food etc..

It will be great if you can come up with some solution for the above. Meanwhile I am also working on that..

Can you help me ? Thank you...
RR

 

[vasah20]

There are a bunch of ways you can do this, and like you said the key issue is your database design.

As long as you issue a key for each location, and possibly issue a key for all the possible types of station, you can then use that composite key as a reference for your calendar table. You would use the composite key to keep track of what a location's station is serving on any given day. That should be enough to keep your database fairly normalized.

As for your GUI, it depends on the setup. If there is going to be daily updates, then you need to make sure that each location has software to get these updates. As for the menu coordinator, why not just give him/her a secure web based intranet interface, and issue the updates that way? Populate some drop down lists, radio buttons or checkboxes using the DB data, and you should be halfway home.

The only problem that you might hit is whether or not your database is accessible by every location, or whether or not the db is local. Personally, I think that the DB should be accessible by every location, so that updating each individual client becomes almost trivial.

just my thoughts
leo leo

 

[foxbox]

[i would build a webside around a central database].

o We need a 'location' table. we can use this table for validating. as a pick list, etc

o is the number and kind of stations on every location the same?
if not:
you need a table to store the stations per location (again: validation, otherwise a menu will show something for a non-existent station).
if yes:
depending on the number of stations you may skip building a seperate table, and hard-code them (not the preferred way, but i presume the number of stations is relative small and fixed)

o you need a table to store the items. Are all stations on all locations able to serve the same items?
if yes:
you can store station/item in a table.
if no:
you must store all location/station/item combinations

o From your description it is not clear if the menu is the same everywhere. I presume not. So your menu-table stores the combination date/location/station/item.


As for a UI:
First the MenuCoordinator is presented a form with location (SELECT) and date fields. After submitting, the database is collecting the menu-records for that combination.

The menu is presented in a 'browse'-form with edit/delete/add option. The Edit/Add form layout is dependent on some of the questions above. The number of stations hopefully is small), so you can show them all on one form, with SELECT field for items.














br
Gerard

 

[Deadline]

Leo ,
Gerard,
Those were insightful responses.. Thank you very much.
Now if you have time, Let me give you my present database I have developed . Can you comment on that ?
Thank you...
RR

 

[vasah20]

post your DB specs here. We'll be able to tell you if we agree with it or not leo

 

[Deadline]

Hi,

The following is the design at the moment.. If you can suggest some improvements, it will be great..

The table EVENT is to handle the food festivals, and special events etc..


----------------------------------------------------

If Exists (Select * From sysobjects Where name = N'FDSVS_EVENT' And user_name(uid) = N'dbo')
Drop Table dbo.FDSVS_EVENT
Go
Create Table dbo.FDSVS_EVENT
(
EVENT_ID int Not Null Identity (1, 1),
DATE decimal(18, 0) Null,
EVENT_NAME varchar(50) Not Null,
S_DESC text Null,
L_DESC char(10) Null,
LOCATION_ID numeric(18, 0) Not Null,
GRAPHIC varchar(50) Null,
STATION_ID int Not Null
)
Go
Alter Table dbo.FDSVS_EVENT Add Constraint
PK_FDSVS_EVENT Primary Key Nonclustered
(
EVENT_ID
)
Go
Alter Table dbo.FDSVS_EVENT Add Constraint
FK_FDSVS_EVENT_1 Foreign Key
(
LOCATION_ID
) References dbo.FDSVS_LOCATION
(
LOCATION_ID
)
Go
Alter Table dbo.FDSVS_EVENT Add Constraint
FK_FDSVS_EVENT_2 Foreign Key
(
STATION_ID
) References dbo.FDSVS_STATION
(
STATION_ID
)
Go

GO
If Exists (Select * From sysobjects Where name = N'FDSVS_FOOD' And user_name(uid) = N'dbo')
Drop Table dbo.FDSVS_FOOD
Go

Create Table dbo.FDSVS_FOOD
(
FOOD_ID int Not Null Identity (1, 1),
FOOD_NAME varchar(50) Null,
S_DESC text Null,
L_DESC text Null,
VEGETARIAN varchar(2) Null Constraint DF__FDSVS_FOO__VEGET__477199F1 Default ('N'),
NO_ADDITIONAL_SALT_OR_OIL varchar(2) Null Constraint DF__FDSVS_FOO__NO_AD__4865BE2A Default ('N'),
MKT_PRICE char(1) Null,
PRICE money Null,
SERVING_SIZE varchar(30) Null,
GRAPHIC varchar(50) Null,
CUISINE_CODE varchar(25) Null
)
Go
Alter Table dbo.FDSVS_FOOD Add Constraint
PK_FDSVS_FOOD1 Primary Key Clustered
(
FOOD_ID
)
Go

GO
If Exists (Select * From sysobjects Where name = N'FDSVS_LOCATION' And user_name(uid) = N'dbo')
Drop Table dbo.FDSVS_LOCATION
Go

Create Table dbo.FDSVS_LOCATION
(
LOCATION_NAME varchar(20) Null,
LOCATION_ID numeric(18, 0) Not Null Identity (1, 1),
WORKING_HOURS varchar(50) Null,
HEADER_GRAPHIC varchar(50) Null,
BG_GRAPHIC char(10) Null
)
Go
Alter Table dbo.FDSVS_LOCATION Add Constraint
PK_FDSVS_LOCATION1 Primary Key Clustered
(
LOCATION_ID
)
Go

GO
If Exists (Select * From sysobjects Where name = N'FDSVS_STATION' And user_name(uid) = N'dbo')
Drop Table dbo.FDSVS_STATION
Go

Create Table dbo.FDSVS_STATION
(
LOCATION_ID numeric(18, 0) Null,
STATION_ID int Not Null Identity (1, 1),
STATION_NAME varchar(30) Null,
S_DESC text Null,
L_DESC text Null
)
Go
Alter Table dbo.FDSVS_STATION Add Constraint
PK_FDSVS_STATION1 Primary Key Clustered
(
STATION_ID
)
Go
Alter Table dbo.FDSVS_STATION Add Constraint
FK_STATION_LOCATION Foreign Key
(
LOCATION_ID
) References dbo.FDSVS_LOCATION
(
LOCATION_ID
)
Go

GO
If Exists (Select * From sysobjects Where name = N'FDSVS_STATION_FOOD' And user_name(uid) = N'dbo')
Drop Table dbo.FDSVS_STATION_FOOD
Go

Create Table dbo.FDSVS_STATION_FOOD
(
DATE smalldatetime Null,
STATION_ID int Not Null,
FOOD_ID int Not Null
)
Go
Alter Table dbo.FDSVS_STATION_FOOD Add Constraint
FK_FDSVS_STATION_FOOD_2 Foreign Key
(
FOOD_ID
) References dbo.FDSVS_FOOD
(
FOOD_ID
)
Go
Alter Table dbo.FDSVS_STATION_FOOD Add Constraint
FK_FDSVS_STATION_FOOD_1 Foreign Key
(
STATION_ID
) References dbo.FDSVS_STATION
(
STATION_ID
)
Go
----------------------------------------------------
Thank you...
RR

 

[vasah20]

Desparate -
Your DB Schema looks pretty good IMHO, but the only thing that I see is that you are storing a station ID with the events. This implies that each station ID has it's own unique event, correct? Why not just store the event_ID in the fdsvs_station_food table?
Since the dates are bound to overlap, you don't risk having duplicate event data in the event table. You can allow NULLS for the event_id column in the station_food table, because not every station will have a corresponding event. By doing it this way, you can even drop the location_id from the fdsvs_event table, since it's already referenced in the fdsvs_station table. So ultimately, you would be about three joins away from getting anything you need.

But - I don't know your dataset, so it might actually make more sense to implement your DB the way that you have.

Anyhow - other than that, your tables look pretty good to me, and also look in the 3NF.

hth
leo leo