con't allow creating table with 50 fields ?
Posted: Fri Oct 24, 2008 7:42 am
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
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
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.VladSun wrote:A table with 50 fields sounds like a very denormalized DB!
The cart table for an ecommerce site I maintain...VladSun wrote:Huh?!?
Can you give me an example
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
versionWe 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:Well, first I see some xxxx_1 ... xxxx_N fields.
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: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?
shipping_county ... not country. There's no duplicate columns there.VladSun wrote:Third, what about these:
shipping_country_id, shipping_country and related ones
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:I'm not sure what are these:
created, dispatched, etc.
They seem to be one time write only. And you have a status.
True, but it is related to the order.VladSun wrote:Also, I doubt that shipping_tracking has something to do with the credit card itself.
They're (potentially) required for every order... not everyone uses a voucher but they could do.VladSun wrote:And there are some vaucher related fields which I think are not required for every card.
It's everything related to an individual order.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
Code: Select all
card.FK_address_id -> address.id, FK_city_id -> city.id, FK_county_id -> county.idYou lost me with this oneonion2k wrote:It's everything related to an individual order.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
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.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
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.VladSun wrote:You lost me with this oneonion2k wrote:It's everything related to an individual order.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
I thought we were discussing the CreditCard table.
Cart like http://en.wikipedia.org/wiki/Cartonion2k 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.
Unusual, not impossible. It does happen, but only for about 0.5% of orders.VladSun wrote:If a credit card data is unusual to be reused, isn't it the same with the "user" data...