I am trying to understand the database design for an e-commerce site. I am having trouble understanding on what to do in the following situation. Say the user creates an account for the first time, and makes an order. I can write php code which will add the user_id (primary-key), first name & last name. But what if I want to add the user_id in the orders table (user_id in the orders tables is a foreign key). How do I get the value, as the user_id in the customers table is auto incrementing?
Are you using two tables one for storing user details and other for placing orders ?(which is better) If so my recommendation would be to use username (not first name or last name) as primary key and store the username as SESSION variable for each login and for each order add the username in order table along with orders details
There are couple of ways to do that, you can access the newly generated user_id by LAST_INSERT_ID() and use it in subsequent transaction, or you can store it in session and use it later when inserting in Orders table.