Should I create tables with dynamic SQL?

  • Hello all,

    I am thinking of creating a "shopping cart" in our application.  My thoughts are using the User's user name as the table name, and the fields are already predetermined.  When the user clicks "Add to Cart", the table would be created.  This could potentially create up to 30 tables if people use this feature.  Then, if they decide to move forward with the "purchase", the data in the table would be inserted into a permanent table, the user table deleted.  If they decide to change vendors, the table would be cleared of any data and the new records inserted if they choose an item from the new vendor.

    I've been reading up on dynamic SQL, and it seems something like this would be possible, but I am not sure if this would be the way to do it.

    Any thoughts?

    Thanks, Mike

  • With great power comes great responsibiity!

    Yes it is techincally possible but I would strongly dicourage it as a design pattern.  Much better to create a properly structured set of tables for use in a multi-tenant environment, with a tenant identifier (user ID) in the CartInstance header table and all the other tables relying on the CartInstance.ID

    How are you going to report and performance mangage the tables when you don't know what they will be called, How will you know which tables need to be cleaned up because a cart session was abandoned.

    How do I know this is a bad design pattern? well  GreatPlains (before it became Dymiacs-GP) when displaying a list of data (e.g. customer accounts) to a user, would run a query, record the results in a persisted temp table and use the temp table to render the details to the front-end application and that persisted temp table was unique for that system user, however it was really bad at clearing the tables up if a session was closed unexpectedly.  I had maintenance jobs that deleted literally hundreds of orphaned tables each night.

  • I agree with Aaron above. I don't like this as a design pattern. I'd much rather have a permanent table that stores transient data, or even another caching store like Redis, for potential data that is modeled well and then "moved" to a permanent order table/structure when the data needs to be persisted.

    Cleanup is certainly an issue, as is a reconnection by a client that needs additional checking to decide to query or recreate the table. That adds complexity that is easy to mess up. Not for you, but for future devs that might add features or refactor code. Simpler to store this in a known place, with a multi-tenant structure and do cleanup of rows rather than actual objects.

  • I agree with Aaron above. I don't like this as a design pattern. I'd much rather have a permanent table that stores transient data, or even another caching store like Redis, for potential data that is modeled well and then "moved" to a permanent order table/structure when the data needs to be persisted.

    Cleanup is certainly an issue, as is a reconnection by a client that needs additional checking to decide to query or recreate the table. That adds complexity that is easy to mess up. Not for you, but for future devs that might add features or refactor code. Simpler to store this in a known place, with a multi-tenant structure and do cleanup of rows rather than actual objects.

  • Creating and dropping tables is significant overhead.  You'd be much better of, as others have noted, with a permanent table.  You could key rows in that table by user id (or user name, if you just can't assign an id).

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • aaron.reese wrote:

    With great power comes great responsibiity!.

      Thank you, Uncle Ben 🙂

    aaron.reese wrote:

    properly structured set of tables for use in a multi-tenant environment

    Ok, so I need one table for storing the Cart data with a user identifier.  One table for storing the purchase history.  Anything else?  And just to be clear, "multi-tenant" means multi-user, correct?

    Steve Jones - SSC Editor wrote:

    caching store like Redis

      What is Redis?

    Steve Jones - SSC Editor wrote:

    as is a reconnection by a client

    reconnecting should be simple, since they login with their user name, then a quick check to see if they have an "open" cart would be easy.

  • You *might* also want some background process to remove carts after a certain period of time, or maybe not.  For example, I can go into Amazon, put something in my cart, and I come back 5 days later and it's still there ... that's kinda nice actually.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Yeah, I like that idea.  For instance, if our maintenance crew is prepping for a project, they could use their perpetual cart to add/edit/delete until their list is complete.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply