April 16, 2024 at 1:59 am
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
April 16, 2024 at 8:42 am
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.
April 16, 2024 at 11:59 am
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.
April 16, 2024 at 12:10 pm
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.
April 16, 2024 at 2:42 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 16, 2024 at 4:00 pm
With great power comes great responsibiity!.
Thank you, Uncle Ben 🙂
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?
caching store like Redis
What is Redis?
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.
April 16, 2024 at 6:17 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 17, 2024 at 4:30 pm
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