con't allow creating table with 50 fields ?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: con't allow creating table with 50 fields ?

Post by VladSun »

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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: con't allow creating table with 50 fields ?

Post by onion2k »

VladSun wrote:You mean that a single user will reuse his credit card only 0.5% of his orders?
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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: con't allow creating table with 50 fields ?

Post by onion2k »

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.
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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: con't allow creating table with 50 fields ?

Post by VladSun »

onion2k 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.
Denormalization :)
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.
onion2k 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.
Denormalization, again :)

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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: con't allow creating table with 50 fields ?

Post by VladSun »

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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply