database architecture

[phppup]

I am considering a two step order form.

The first webpage would require basic information such as name and email.

The second webpage would include pricing and submission.

The reason for the two step separation is so that the basic data can be inserted into a table. In the event that an order is not submitted, these people can be solicited separately (perhaps with discounted pricing or a survey).

The real question pertains to the best architecture for such a database.

My initial thought is that I need a table for data [BasicInfo] generated from webpage_1, and another table for people that actually place orders [CustomerTable] with an associated table for the actual order and pricing [OrderTable].

Does every completion of webpage_1 go into the BasicInfo table, with a repeat of basic info and order info being directed to CustomerTable and OrderTable, respectively?(This seems unnecessarily redundant where BasicInfo is really a list of both costumers and visitors)

So what is the best way to trim the fat?

Put everyone in BasicInfo and then move the row if they place an order?

Put everyone in BasicInfo with a column named 'order' that is updated for future referencing?

Some other method that I haven't thought of yet?

Advice please.

[requinix]

One table for orders. They can be marked as pending/not complete. That's okay. They can have incomplete information - while still pending. That's okay.

[phppup]

@Requinix.... Sorry, I'm just so used to posting in the coding section and the question arose while I was considering the code that needed to be written.  I will be more careful in the future.

As for my concern, I'm more interested in how to structure the USER data accumulated from visitors that enter partial information versus those that provide additional information (and an order).  The idea being that if webpage_1 gathers basic information, then a visitor that declines providing more data on webpage_2 (after seeing cost/pricing) can be contacted with a survey to determine what discouraged him.

Obviously, a visitor that DID provide additional information and became a customer would NOT be sent the same survey.

So, from a coding perspective, do I insert all webpage_1 data into a table BasicInfo and then move it OR duplicate it into a table CustomerInfo if the customer completes webpage_2 ?

Or do I simply lump everyone into a table of BasicInfo (for ALL visitors), perhaps with a column indicating whether they were only a visitor or an actual customer?

[requinix]

21 minutes ago, phppup said:

As for my concern, I'm more interested in how to structure the USER data accumulated from visitors that enter partial information versus those that provide additional information (and an order).  The idea being that if webpage_1 gathers basic information, then a visitor that declines providing more data on webpage_2 (after seeing cost/pricing) can be contacted with a survey to determine what discouraged him.

If that happens then, with my description, the order will be marked as pending and only have some fields filled in. Or maybe it has everything filled in but it's still pending, not sure. Either way, you can query the table to find these people.

Also not sure about this user vs. visitor distinction, but I believe the answer is still what I said: the order is pending, has some information filled in, does not have the "user ID" part of it provided, and you can query for that.

[phppup]

An order would ONLY be placed on webpage_2 (along with pricing).  So, the distinction isn't the status of the order, but rather the classification of the visitor (ie: browser or customer).

Does that change your thinking?

If I get 100 visitors and only 20 become customers (on their first visit) do I want one single table of all visitors, or two distinctly different tables [BasicInfo with 80 rows, CustInfo with 20 and an association to their placed orders]?

[requinix]

One table. You create the order in a pending state when they complete the first page. You can update that order with information from the second page, with user information if/when you get it, with whatever. When all the requirements are met you can switch the order to complete or ready or whatever.

[phppup]

Thanks.