Database Design for Multiple Dates

[dinovitz]

Hi,

I am new to MS Access 2000, and am designing a database for which the Northwind database provides a good model. The twist in my design is that the products associated with each order will have their own ship dates, expected ship date, and status (eg. not shipped) etc. So instead of the ship dates being associated with the order, they are associated with the product.

If you have any suggestions on how to set up these product date and status fields, and to then associate them with the order I would really appreciate it.

Thanks!
RD

 

[JerryDennison]

Each product should be a seperate record in the order details subtable. As such you can add fields to this table for Date Due and Date Shipped. If Date Shipped is null you can assume it hasn't shipped so you don't need a "status" field per se. Just test to see if it has a shipped date.

 

[JerryDennison]

BTW, if you expect each line item to possibly have more than one ship date (i.e. back order situations) then add another table instead of fields to the existing one. This table would have a link to the order detail so you can capture the ship date(s) and quantity for each line item.

 

[dinovitz]

Thanks so much for your reply!

I will have multiple dates, so would all dates go into this additional table, or only "backorder" dates? What would then be captured in the order details table? And just so I understand, what is the logic for creating this additional table?

Thanks again ....
RD

 

[JerryDennison]

The logic is that you have described a one-to-many relationship between each line item record and the potential quantity and date shipped. You will need to capture each transaction as it occurs. For example, you customer order 500 widgest but you only have 300 in stock. You send out 300 today leaving a balance of 200 for the line item on their original order. 2 days later you receive 100 widgets and send them to the customer... get the picture? There is no way you can capture these transactions if you included the ship qty and dates in the order details table.

 

[dinovitz]

This makes perfect sense. Thanks. But I am having trouble constucting this table.

Would I create a BackorderDetails table, that is linked to the OrderDetails table through a BackorderID field? The OrderDetails table dos not have its own Order DetailsID as set up in Northwind, so I am a bit stumped.

Thanks again.

 

[JerryDennison]

No, you would create a OrderDetailsShipped linked to your OrderDetails via a foreign key to the order detail's primary key. It would not be used just for back orders but for all shipping transactions for each line item of the order detail. You would probably want to use some code to facilitate easier processing of the line item details as you will require a record for each line item in the orderdetails table. The rule will be that everything ordered is shipped at the same time and backorders would be the exception. You should always program for the rule making it the easiest to process.