Field Type for SQL Table
-
Hi, I was wondering if someone could point me in the right direction for the following problem: I created a SQL database which includes a payment system to process customer payments, when defining the Table which will hold the Payments from customers, I couldn't decide if i should create a table for Payment Types (cash, credit card, checking etc...) and add a relationship for each customer payment to the Payment Type table, or use a set of predefined characters to identify payment types (S=cash, C=credit card, H=checking etc...) A payment type table is definitely more in compliance with the relational database modal, but on the other hand, since i will at most only have 4 or 5 payment types it will offer much better performance since there is no joins and PK/FK constraints and it will save space as well.
-
Hi, I was wondering if someone could point me in the right direction for the following problem: I created a SQL database which includes a payment system to process customer payments, when defining the Table which will hold the Payments from customers, I couldn't decide if i should create a table for Payment Types (cash, credit card, checking etc...) and add a relationship for each customer payment to the Payment Type table, or use a set of predefined characters to identify payment types (S=cash, C=credit card, H=checking etc...) A payment type table is definitely more in compliance with the relational database modal, but on the other hand, since i will at most only have 4 or 5 payment types it will offer much better performance since there is no joins and PK/FK constraints and it will save space as well.
Yet at you end up having a performance overhead when you end up having to convert the payment type back. Plus, what happens if you add a payment type? Add a row and get the functionality for free, or change a hunk of code to handle the new Quarterly Cash payment type that your customer wants? Stick with the normalised version - the overhead of the fk isn't that great.
Deja View - the feeling that you've seen this post before.
-
Yet at you end up having a performance overhead when you end up having to convert the payment type back. Plus, what happens if you add a payment type? Add a row and get the functionality for free, or change a hunk of code to handle the new Quarterly Cash payment type that your customer wants? Stick with the normalised version - the overhead of the fk isn't that great.
Deja View - the feeling that you've seen this post before.
You have a very good point about the performance (although its not in the db its in the app which is generally cheaper) The problem of adding a new payment type hit me as well. Initially i decided i can save a list of payment types in a configuration file and add the new one there, i figured its not that often that i will be adding a new payment type anyway. But why reinvent the wheel when i can have that list saved in the db itself. Thanks for the reply
-
You have a very good point about the performance (although its not in the db its in the app which is generally cheaper) The problem of adding a new payment type hit me as well. Initially i decided i can save a list of payment types in a configuration file and add the new one there, i figured its not that often that i will be adding a new payment type anyway. But why reinvent the wheel when i can have that list saved in the db itself. Thanks for the reply
Yona Low wrote:
But why reinvent the wheel when i can have that list saved in the db itself.
Lovely jubbly. You know it makes sense.:-D
Deja View - the feeling that you've seen this post before.