Solved Auto Create record in TblOrderDetails for the order when we create it in TblOrder (2 Viewers)

[Hisoka]

Hello,

I have a database that includes the following tables:
Table 1:
Customers (CustomerID, FirstName, LastName, Number) The primary key is CustomerID

Table 2:
Orders (OrderID, CustomerID, TheProduct, Quantity, OrderDate) The primary key is OrderID

Table 3:
OrdersDetails (OrderID, OrderStatus (whether it has been shipped or not), ShippedDate) The primary key is OrderID.

As you see the Relationship between tblCustomers & tblOrders 1-To-Many
and Relationship between tblOrders & tblOrdersDetails 1-To-1

I have 3 questions :
1- when I create a form for the tblOrders and enter a specific order for a specific customer, I want the program to automatically send this OrderID to the tblOrdersDetails and put in the OrderStatus Field some string for example “Processing.” To be able to deal with it later (I just want to define the order In tblOrdersDetails table to deal with later)..
How can we do this and what is the best way to Handle this scenario?
2- Is this the correct way to create tables for this purpose I mean create those 2 tables (Orders & OrdersDetails) or should combine them in one Table To solve this problem, Knowing I prefer to set the Orders and OrdersDetails it on a different Table?
3- Is it necessary to add CustomerID to tblOrdersDetails or this method apply just for Junction Table when we have Many-To-Many Relationship or this optional?

I have attached the file, I would appreciate any help

 

[plog]

Relationship between tblOrders & tblOrdersDetails 1-1

Why? There's really no reason for a 1-1 relationship, just put the fields in tblOrderDetails in tblOrders.

I mean, you put First and Last Name in tblCustomers not in a table called tblCustomerDetails table in a 1-1 relationship with tblCustomers. You simply put that data in tblCustomers. Do the same with tblOrderDetails.

 

[Hisoka]

Your words are very logical, Thank you,
But in my case I prefer to separate the order from the Details(Status) of the order (for example, was it shipped or not, was it delivered or not, the date it was delivered to the customer, etc.)

 

[Pat Hartman]

The point of separating OrderDetails from Order is to allow you to order multiple products on a single order.

But in my case I prefer to separate the order from the Details(Status) of the order (for example, was it shipped or not, was it delivered or not, the date it was delivered to the customer, etc.)

Relationships are not arbitrary. You are deliberately creating confusion in your schema by separating something that there is no reason to separate except for your personal "preference".

TheProduct and Quantity belong in the OrderDetails table. Having the shippedStatus in the OrderDetails means that for your application, you have decided to make partial shipments of individual items. That is fine and that is a reason for keeping the shippedStatus in the Details table.

Even if today you sell only ONE product, there is no reason to hobble the database schema with that limitation. Use a normal schema that allows multiple items per order and then thank me when some time in the future you add a second item to your Products table.

 

[Hisoka]

I get it @Pat Hartman, Thanks for the clarification.
Regarding the separating of tables, it is not a matter of my preference, I just wanted to create an database according to standard rules and Now I and understand and convinced that my method was wrong..
You are right if we have more than one item in one order, my method will cause a lot problems

Thank you for you and for @plog

 

[jdraw]

Hisoka, you might want to watch the Northwind 2 youtube videos. Kim Young and Tom Van Stiphout talk about design and inventory. Very interesting discussion.