List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:May 19 2006 12:11pm
Subject:Re: Is This A Redundant Info Example?
View as plain text  
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
Thread
Is This A Redundant Info Example?Mark Sargent19 May
  • Re: Is This A Redundant Info Example?Martijn Tonies19 May
    • Re: Is This A Redundant Info Example?sheeri kritzer19 May
  • Re: Is This A Redundant Info Example?Martijn Tonies19 May
  • Re: Is This A Redundant Info Example?John Hicks19 May
RE: Is This A Redundant Info Example?Jay Blanchard19 May
RE: Is This A Redundant Info Example?Bob)19 May
RE: Is This A Redundant Info Example?Jay Blanchard19 May
  • Re: Is This A Redundant Info Example?sheeri kritzer19 May
  • Re: Is This A Redundant Info Example?Martijn Tonies20 May