StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat ways are there to store information about an anonymous/guest user in a database?
text
Body
copied!<p>Our application has an online shop among other features, and users are normally requested to register before completing a sale, creating a unique <code>customer_ID</code> in the process. When they return, they can log in and their contact details and transaction history are retrieved from the database.</p> <p>We are now exploring what to do in the case of an 'anonymous' or 'guest' customer, opening up the online shop to customers who don't want to register, and also for sales logged in the backend application, where taking the customer's email, postal address, etc is just too time consuming. The solution has applications outside the online shop too.</p> <p>Multiple companies use the same database, and the database is built on a <a href="https://stackoverflow.com/questions/716549/what-are-the-principles-behind-and-benefits-of-the-party-model">party model</a> structure, so we have explored a few options:</p> <ol> <li>Store all anonymous customers under one pre-defined <code>customer_ID</code> in the <code>transaction</code> table: <ol> <li><code>customer_ID = 0</code> for every anonymous user, and <code>customer_ID > 0</code> for every real user <ul> <li>This is straight-forward to hard-code into the application</li> <li>But more involved to determine which customers belong to which company</li> <li>Should details for <code>customer_ID = 0</code> exist in the <code>customer</code> table in the database or as an object in the application? <ul> <li>If in the database, what database-level constraints can be made to ensure that it always exists?</li> <li>If not in the database, then foreign key constraints from <code>transaction.customer_ID</code> to <code>customer.customer_ID</code> no longer work</li> </ul></li> </ul></li> <li><code>customer_ID</code> is the same as the company <code>party_ID</code> <ul> <li>Easier to determine aggregate sales for each company, etc</li> <li>This would confuse matters as it would appear that the company is its own customer, rather than other unique customers</li> </ul></li> </ol></li> <li>Generate a unique <code>customer_ID</code> for every new anonymous customer (per session) <ul> <li>What if the same physical user returns? There will be many records repeating the same sort of data; email, shipping address, etc.</li> </ul></li> <li>Use another unique key, such as email address, to refer to a customer <ul> <li>Not always reliable as people sometimes use more than one email address, or leave old addresses behind.</li> <li>What if there is no email address to be taken, as is the case on the shop floor, pro forma invoices, etc?</li> </ul></li> <li>Some other Stack Overflow inspired solution!</li> </ol> <p><strong>Addition</strong></p> <p>A combination of #2 and #3 has been suggested elsewhere - attempt to store a single record for each customer, using the email address if possible, or a new record on every visit if not.</p> <p>I should point out that we don't <em>need</em> to store a record for every anonymous customer, but it just seems that the relational database was built to deal with relationships, so having a NULL or a <code>customer_ID</code> in the <code>transaction</code> table that doesn't reference an actual customer record just seems wrong...</p> <p>I must also stress that the purpose of this question is to determine what real-world solutions there are to recording 'casual' transactions where no postal address or email address are given (imagine a supermarket chekout) alongside online shop transactions where an email address and postal address are given whether they are stored or not.</p> <p>What solutions have the SO community used in the past?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload