I am trying to modify the Webshop described in Chapter 9 of Asp.net 2.0 instant results. I need to add a date field. I am creating a food order application so for example my category would be Wendy's, products would be french fries, hamburger, etc. and date of meal would be 11/6/2006. So I could have 2 dates in the system, each with Wendy's as the category with the same products. I was thinking I should add the date field to the category table and make category + date the unique field linking each product to a category and date but I'm not sure this is the best approach. Any thoughts would be greatly appreciated!

Thanks

Hi saf,

I don't understand what you're asking exactly. Can you elaborate a little bit and maybe give some examples?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

OK - here goes...
My question is more of a basic database design issue.

The main part of the site will be an order form where users will be ordering meals for certain days. Each Category/Date combination needs to be unique as you could order Chinese on 12/1 and/or on 12/4. For example, the main page will look something like this:

Friday, Dec 1
product price qty subtotal (product/price pulled from database, user enters qty)

Monday, Dec 4
product price qty subtotal

So I somehow need to incorporate the lunchdate into the database design. I guess I'm just not sure what table(s) to add the date field to and how to create the relationships so I minimize the corresonding changes to the application.
I hope I'm explaining myself better. Thank you!

Yes, I understand it better now.

So, technically each dates makes a product unique. So, technically you would need to store the date with each product: Chinese on wednesday 10th is different from Chinese on the 11th.

But obviously, you don't want your users to create hundreds of different, but very alike items.

So, instead, I would store the dates in a separate table, like AvailableDate, and create a junction table between this table and the product table, for example: ProductAvailableDate.

Then in the CMS of the shop, a user can select multiple dates (with a Calendar and a GridView for example) that are applicable for a product.

Next, when you query for the relevant products, you can join the Product and the AvailableDate items so you get the relevant product / date combinations. Each product you add to List (Of Poduct) in the GetList method can have an AvailableDate property that you can show on the site.

Does this put you in the right direction? The chapter Appointment Booking Application has something similar. The CMS allows you to make booking objects available for certain days only. Then the bonus walkthrough of that chapter guides you through creating an exception day functionality, which is more or less the reverse of what you're trying to do.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

Thank you! This definitely points me in the right direction. I'm new to asp.net and your guidance is greatly appreciated!

saf

You're welcome.

Feel free to ask more questions if you need more guidance.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.