I'm designing an e-commerce database. Tables:
payment_methods
---------------
id
method
provider
orders
------
id
user_id (FK)
payment_method (FK)
products_price
shipping_cost
total
payment_date
status
created_at
Many websites create a separate table for payment details related to each order.
Is there a need to split orders and create a table (payments) like the following?
payments
--------
id
order_id
payment_method
provider
total
created_at
As per the insight given in the comment from @Charlieface, it comes down to how you plan to use the data.
This falls under the topic of Normalization. If you would like to keep things simple with the realization that it may need to be changed later, keeping payments in order can work, though my tendency is to split thing apart to maintain flexibility.
Besides semantics and simplicity, other considerations include performance and readability of your database. For readability and select-ability, a fat, de-normalized table works well (assuming it semantically makes sense and each record is complete). The downside is when you want to look at payments apart from orders and any updates to orders or payment will affect the same record. There is a tendency for large tables with many updates having performance problems in the long-term. It can also make it more difficult/slower to change those tables later simply due to size.
In summary, do what makes sense and you are willing to deal with, realizing the trade-offs.