Hello Mark, > I am looking at OSCommerce for design comparisons and wonder what others > think about the below table design, > > CREATE TABLE `customers` ( > `customers_id` int(11) NOT NULL auto_increment, > `customers_gender` char(1) NOT NULL, > `customers_firstname` varchar(32) NOT NULL, > `customers_lastname` varchar(32) NOT NULL, > `customers_dob` datetime NOT NULL default '0000-00-00 00:00:00', > `customers_email_address` varchar(96) NOT NULL, > `customers_default_address_id` int(11) default NULL, > `customers_telephone` varchar(32) NOT NULL, > `customers_fax` varchar(32) default NULL, > `customers_password` varchar(40) NOT NULL, > `customers_newsletter` char(1) default NULL, > PRIMARY KEY (`customers_id`) > > > > CREATE TABLE `orders` ( > `orders_id` int(11) NOT NULL auto_increment, > `customers_id` int(11) NOT NULL, > `customers_name` varchar(64) NOT NULL, > `customers_company` varchar(32) default NULL, > `customers_street_address` varchar(64) NOT NULL, > `customers_suburb` varchar(32) default NULL, > `customers_city` varchar(32) NOT NULL, > `customers_postcode` varchar(10) NOT NULL, > `customers_state` varchar(32) default NULL, > `customers_country` varchar(32) NOT NULL, > `customers_telephone` varchar(32) NOT NULL, > `customers_email_address` varchar(96) NOT NULL, > `customers_address_format_id` int(5) NOT NULL, > `delivery_name` varchar(64) NOT NULL, > `delivery_company` varchar(32) default NULL, > `delivery_street_address` varchar(64) NOT NULL, > `delivery_suburb` varchar(32) default NULL, > `delivery_city` varchar(32) NOT NULL, > `delivery_postcode` varchar(10) NOT NULL, > `delivery_state` varchar(32) default NULL, > `delivery_country` varchar(32) NOT NULL, > `delivery_address_format_id` int(5) NOT NULL, > `billing_name` varchar(64) NOT NULL, > `billing_company` varchar(32) default NULL, > `billing_street_address` varchar(64) NOT NULL, > `billing_suburb` varchar(32) default NULL, > `billing_city` varchar(32) NOT NULL, > `billing_postcode` varchar(10) NOT NULL, > `billing_state` varchar(32) default NULL, > `billing_country` varchar(32) NOT NULL, > `billing_address_format_id` int(5) NOT NULL, > `payment_method` varchar(32) NOT NULL, > `cc_type` varchar(20) default NULL, > `cc_owner` varchar(64) default NULL, > `cc_number` varchar(32) default NULL, > `cc_expires` varchar(4) default NULL, > `last_modified` datetime default NULL, > `date_purchased` datetime default NULL, > `orders_status` int(5) NOT NULL, > `orders_date_finished` datetime default NULL, > `currency` char(3) default NULL, > `currency_value` decimal(14,6) default NULL, > PRIMARY KEY (`orders_id`) > > Why have customer info in both? Delivery and Billing info makes sense, > but why the redundant info in both? Anyone got views on this? Do/would > you do it differently, and could you tell us why? Cheers.
Well, one reason could be, for example, that the address changes over time and they want to know what address the customer was when the order was processed. Hogwash IMO though ;-) I wouldn't enter the "delivery" and "billing" info either, but create some sort of "customer_address" table or "order_customer_address" in which records would be inserted if and only if the addresses used for billing and delivery differ from the normal customer address. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
