#sintonz-database
quartz yoke
@fiery wyvern I think we can try answer any specific questions you have, but designing a schema with you is a bit too in-depth/out of scope for what we can do over Discord. Was there any specific difficulty or question you ran into?
fiery wyvern
Well I am planning on adding multiple payment methods with stripe and other resources like btcpay to my website. I just don't know how to do it in the best way. I know there is https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database and normalization techniques also but I am mainly looking for a schema design (since my database is not in production) that can work for lots of payment methods and different user membership types.
quartz yoke
I don't think we have a schema we can directly just give you. You could maybe look at Sigma(https://stripe.com/docs/sigma) in your dashboard (https://dashboard.stripe.com/sigma/queries) and see the schemas we use there to take inspiration.
gilded shadow
Hi 👋 I'm stepping in for karllekko. As they mentioned, helping design your DB schema is difficult to do without a thorough understanding of what you're trying to accomplish and that's outside of our scope here.
fiery wyvern
Ok thanks for stepping in. What details do you need or are confused about?
Basically in the dpaste I have all those stripe tables then I added on the btcpayserver table so now I have both Stripe and crypto payments. When someone makes a payment (either stripe or crypto) the Subscription table active column becomes 1 (true) and the UserMembership membership column becomes either Monthly/Yearly depending on which plan the customer bought
All I am looking for is a schema design (like how the first stackoverflow link did it - or like how I have it in my dpaste) but in a new way that is more optimal to handle multiple payment types and memberships.
To me, it does not make sense for stripe related data in some of these tables and instead maybe there should be a separate table just for Stripe data
I am mainly looking for a best practices answer or your opinion on how you think it should be designed since I am not a database expert myself. I have the flexibility to make changes since the db is not in production so there will be no breaking changes
All I am looking for is where the columns should be placed and what tables they should go in.
gilded shadow
I don't understand what you mean by "more optimal to handle multiple payment types and memberships". It's unclear what you see as the limitation of your current approach, as well as what your current approach is.
fiery wyvern
I think what will happen is if I make a crypto payment, there will be NULL columns in some of these tables that is asking for Stripe data like is highlighted here https://dpaste.org/fiw0#L6,11,17,25
This is my attempt at making things better https://dpaste.org/e7Fg but I am not sure if it is correct.
As you can see, I am removing the stripe API id's from some of the tables and moving them to their own table.
That to me is better than the original dpaste but I am not sure if it is the best way.
gilded shadow
I'm also no DBA, so I won't have much advice to offer. It's unclear to me what you plan to use as foreign keys amongst your tables, but if you're planning to use username then your model may not handle a user having multiple subscriptions. I'm not sure whether that is a concerning scenario because I don't understand what you're trying to build. My advice is to create a model, mock the flow of data through it, and see if it will work for your scenarios. Apologies but we won't be able to give any more direct advice on this topic.
fiery wyvern
username is the FK for all the tables yes
a user can only have 1 membership at a time
rapid fulcrum
I'm catching up here, give me a sec
fiery wyvern
Ok sure thing