Page 1 of 2

con't allow creating table with 50 fields ?

Posted: Fri Oct 24, 2008 7:42 am
by manohar
I am trying to create a table with 50 fields but i am getting error can u pls tell me what is the problem and where the error getting

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

Posted: Fri Oct 24, 2008 7:45 am
by oscardog
Well in order to help, we would need the code you are inputting to try and generate the table, and what error msg it is giving.

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

Posted: Fri Oct 24, 2008 7:47 am
by VladSun
A table with 50 fields sounds like a very denormalized DB!

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

Posted: Fri Oct 24, 2008 8:19 am
by onion2k
VladSun wrote:A table with 50 fields sounds like a very denormalized DB!
I have tables with many more columns than that. Sometimes you just need to store lots of different things about 1 item. Consequently you get lots of columns.

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

Posted: Fri Oct 24, 2008 8:23 am
by VladSun
Huh?!?
Can you give me an example :)

Most of the tables with more than 10 fields, I have seen, contain intentionaly "denormilized" fields and fields that can be calculated from other tables but they exist due to estiamtion simplicity.

Of course, there might be such tables (normalized), but these would be very rarely seen.

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

Posted: Fri Oct 24, 2008 8:46 am
by onion2k
VladSun wrote:Huh?!?
Can you give me an example :)
The cart table for an ecommerce site I maintain...

Code: Select all

cart_id
order_type_id
user_id
site_id
affiliate_user_id
affiliate_code
affiliate_plan_id
affiliate_withdrawal_date
order_ref
epdq_ref
protx_ref
protx_securitykey
3d
order_status_id
shipping_free
shipping_address_set
shipping_type_id
shipping_name
shipping_address_1
shipping_address_2
shipping_address_3
shipping_address_4
shipping_towncity
shipping_county
shipping_postcode
shipping_country_id
shipping_tracking_courier_id
shipping_tracking_code
shipping_tracking_note
card_type_id
card_number
card_csc
card_startdate
card_expirydate
card_issuenumber
card_name
card_address_1
card_address_2
card_address_3
card_address_4
card_towncity
card_county
card_postcode
card_country_id
sub_total
vat_registered
shipping_total
giftvoucher_total
offervoucher_total
amount_paid
amount_refunded
payment_status
created
checkout
dispatched
auto_register
hear_about_id
advert_id
status
locked
testimonial_request_sent
followup
version
63 columns, all related to the cart.

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

Posted: Fri Oct 24, 2008 3:05 pm
by VladSun
Well, first I see some xxxx_1 ... xxxx_N fields.
Second, I feel pretty sure that you don't have a credit card type which insists that ALL of these fields should be filled with non empty data (even without the fields I mentioned above) - am I right?

Third, what about these:
shipping_country_id, shipping_country and related ones

I'm not sure what are these:
created, dispatched, etc.
They seem to be one time write only. And you have a status.

Also, I doubt that shipping_tracking has something to do with the credit card itself.

And there are some vaucher related fields which I think are not required for every card.

To summarize - I feel like you are mixing CreditCard, Order, Shipping, Tracking and some other things which names I can't figure out :)

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

Posted: Fri Oct 24, 2008 3:27 pm
by onion2k
VladSun wrote:Well, first I see some xxxx_1 ... xxxx_N fields.
We dynamically generate invoices with things like addresses being dependent on what the user's location is ... postcode becomes ZIP code for example. Consequently we need the first 4 lines of the address to be entered separately because they can represent different things on different invoices. If I was rewriting it now I'd probably break them up into more fields rather than consolidating them into 1. Also, in the case of the card address, we use that to check for fraud with the credit card provider, and they insist on it being the same as the user's billing address. Consequently we can't rely on the user to enter it into a single textarea in case they do things like "NE42 1AA, Newcastle, UK" like you'd print on an address label. That would break the card validation.
VladSun wrote:Second, I feel pretty sure that you don't have a credit card type which insists that ALL of these fields should be filled with non empty data (even without the fields I mentioned above) - am I right?
No. Switch (Maestro debit cards) can have all of the various fields at once (according to the card spec). The only ones that are really optional are issue number and start date anyway. It wouldn't be worthwhile separating the entire credit card information into another table just for those 2.
VladSun wrote:Third, what about these:
shipping_country_id, shipping_country and related ones
shipping_county ... not country. There's no duplicate columns there.
VladSun wrote:I'm not sure what are these:
created, dispatched, etc.
They seem to be one time write only. And you have a status.
They're timestamps for when the cart was created, dispatched, etc. The status is a flag for things like test orders so we can ignore the order in reports and exports for accounting.
VladSun wrote:Also, I doubt that shipping_tracking has something to do with the credit card itself.
True, but it is related to the order.
VladSun wrote:And there are some vaucher related fields which I think are not required for every card.
They're (potentially) required for every order... not everyone uses a voucher but they could do.
VladSun wrote:To summarize - I feel like you are mixing CreditCard, Order, Shipping, Tracking and some other things which names I can't figure out
It's everything related to an individual order.

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

Posted: Fri Oct 24, 2008 3:33 pm
by VladSun
OK, sorry about this "county" word :)
But shouldn't, all these address related fields, be represented by a single address_id?
E.g.

Code: Select all

card.FK_address_id -> address.id, FK_city_id -> city.id, FK_county_id -> county.id

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

Posted: Fri Oct 24, 2008 3:34 pm
by VladSun
onion2k wrote:
VladSun wrote:To summarize - I feel like you are mixing CreditCard, Order, Shipping, Tracking and some other things which names I can't figure out
It's everything related to an individual order.
You lost me with this one :?
I thought we were discussing the CreditCard table.

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

Posted: Fri Oct 24, 2008 3:45 pm
by onion2k
VladSun wrote:OK, sorry about this "county" word :)
But shouldn't, all these address related fields, be represented by a single address_id?
E.g.

Code: Select all

card.FK_address_id -> address.id, FK_city_id -> city.id, FK_county_id -> county.id
That's one approach but I figured that there aren't really any places in the system where anything less than all the data is required ... during the checkout, admin, order history, invoice generator, etc ... all the address data will be needed every time the order is displayed, so why bother having to join to a second table every time? Also, none of the address data is reused very often. Most of the customers buy once and don't return so there's very few duplicate addresses.

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

Posted: Fri Oct 24, 2008 3:47 pm
by onion2k
VladSun wrote:
onion2k wrote:
VladSun wrote:To summarize - I feel like you are mixing CreditCard, Order, Shipping, Tracking and some other things which names I can't figure out
It's everything related to an individual order.
You lost me with this one :?
I thought we were discussing the CreditCard table.
The table I posted is the orders ("cart") table. Credit card data is stored for each individual order. It's unusual for it to be reused because there aren't many repeat orders.

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

Posted: Fri Oct 24, 2008 3:53 pm
by VladSun
onion2k wrote:The table I posted is the orders ("cart") table. Credit card data is stored for each individual order. It's unusual for it to be reused because there aren't many repeat orders.
Cart like http://en.wikipedia.org/wiki/Cart :)
Sorry about all these mistakes I've made due to my poor English skills :)

Still I can't agree ;)
If a credit card data is unusual to be reused, isn't it the same with the "user" data...

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

Posted: Fri Oct 24, 2008 3:55 pm
by onion2k
VladSun wrote:If a credit card data is unusual to be reused, isn't it the same with the "user" data...
Unusual, not impossible. It does happen, but only for about 0.5% of orders.

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

Posted: Fri Oct 24, 2008 3:56 pm
by VladSun
You mean that a single user will reuse his credit card only 0.5% of his orders?