I looked at my Order table and it's 15 columns foreign keys + 15 data columns (5 of them are denormalized).
The main difference between my and your shopping system is that my system allows several types of payment. That's why I do not put credit card data into orders.
con't allow creating table with 50 fields ?
Moderator: General Moderators
Re: con't allow creating table with 50 fields ?
There are 10 types of people in this world, those who understand binary and those who don't
Re: con't allow creating table with 50 fields ?
No, I mean that orders are made by a returning customer about 0.5% of the time. If we stored the credit card details in the user table, and one of the repeat customers came back and wanted to buy again but with a different credit card it would all fall apart if the card details were in the user table. That unlikely circumstance stops us storing the credit card data in the user table ... so we can either store it in the cart table, or in a separate table. As 99.5% of people aren't repeat customers so the chance of it being duplicate data is very remote, I decided to put the credit card data in the cart table rather than a separate table.VladSun wrote:You mean that a single user will reuse his credit card only 0.5% of his orders?
Re: con't allow creating table with 50 fields ?
So does mine. We have credit card payment, cash, cheque, BACS transfer and probably some others I've forgotten ... for those orders the credit card columns will be empty. That isn't a problem.VladSun wrote:I looked at my Order table and it's 15 columns foreign keys + 15 data columns (5 of them are denormalized).
The main difference between my and your shopping system is that my system allows several types of payment. That's why I do not put credit card data into orders.
Re: con't allow creating table with 50 fields ?
Denormalizationonion2k wrote:As 99.5% of people aren't repeat customers so the chance of it being duplicate data is very remote, I decided to put the credit card data in the cart table rather than a separate table.
It will be the same as to put this data in another table.
My question was: if you don't feel like having a separate credit card data table, why did you put the user data in a separate one. It seems that in your case user and credit card data have 1:1 relationship.
Denormalization, againonion2k wrote:We have credit card payment, cash, cheque, BACS transfer and probably some others I've forgotten ... for those orders the credit card columns will be empty. That isn't a problem.
Well, it is a problem to have NULL values in a table - it "breaks" indexing and searching by this field.
That's why most people use a separate table (redundant at first look) for implementing such fields.
PS: Could you move this thread to DB forum section
There are 10 types of people in this world, those who understand binary and those who don't
Re: con't allow creating table with 50 fields ?
Some time ago, in one of your posts, you said that "table is not an object - it's just a table".
That's what I can't agree with.
There are "decoupling"-like decisions in DB design.
Let's have four related tables: Order, PaymentType1, PaymentType2, PaymentType3.
In general, you can relate Order to each PaymentType by using one of these:
1) by having FK_PaymentType1_id, PaymentType2_id and FK_PaymentType3_id in Order;
2) by having FK_PaymentType_id and PaymentType_type - i.e. which is the PaymentType table containing the row FK_PaymentType_id refers to, in Order;
3) by having FK_Order_id in all of the PaymentTypeX tables and optionally a PaymentType_type column in the Order table;
I think, number 1 is the worst one - 3 additional fields, 2 of them are always NULL (bad for indexing). Also, the PaymentType is hard-coded in the Order table structure, that's why adding a 4th PaymentType would require changes in the existing DB design - the Order table.
The second one is better, but still in the queries you must know which PaymentType corresponds to a specific PaymentType_type value. No NULL values. Adding a 4th PaymentType would not require changes in the existing DB design.
The third one, when used without the optional PaymentType column is the most "decoupled" one. Even, in the Order table there is no knowledge stored about the possible PaymentTypes.
No NULL values, adding a 4th PaymentType would not require changes in the existing DB design.
You said, that you were able to use other payment types in you shopping system, but I couldn't see how it will be done with your table structure.
That's what I can't agree with.
There are "decoupling"-like decisions in DB design.
Let's have four related tables: Order, PaymentType1, PaymentType2, PaymentType3.
In general, you can relate Order to each PaymentType by using one of these:
1) by having FK_PaymentType1_id, PaymentType2_id and FK_PaymentType3_id in Order;
2) by having FK_PaymentType_id and PaymentType_type - i.e. which is the PaymentType table containing the row FK_PaymentType_id refers to, in Order;
3) by having FK_Order_id in all of the PaymentTypeX tables and optionally a PaymentType_type column in the Order table;
I think, number 1 is the worst one - 3 additional fields, 2 of them are always NULL (bad for indexing). Also, the PaymentType is hard-coded in the Order table structure, that's why adding a 4th PaymentType would require changes in the existing DB design - the Order table.
The second one is better, but still in the queries you must know which PaymentType corresponds to a specific PaymentType_type value. No NULL values. Adding a 4th PaymentType would not require changes in the existing DB design.
The third one, when used without the optional PaymentType column is the most "decoupled" one. Even, in the Order table there is no knowledge stored about the possible PaymentTypes.
No NULL values, adding a 4th PaymentType would not require changes in the existing DB design.
You said, that you were able to use other payment types in you shopping system, but I couldn't see how it will be done with your table structure.
There are 10 types of people in this world, those who understand binary and those who don't