FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


MySQL table relations





Marcuzzo
my (My)SQL is rusty at the moment and I'm trying to figure out if I'm handling the following situation correctly.

I've got an application that will be used by users and they will be creating 'customers'
users and customers have got several value types that are the same. eg: Firstname, lastname.
They also have contact details like a telephone number and an email address.
while I need identity information about customers, like the national number and a date of birth, I do not need to know this information for a user.
same goes for credentials, a user will have a username and a password, while this is not needed for a customer entity

can somebody look as this and tell me if I'm on the right track?

Code:


/*
 * Countries Table
 */
DROP TABLE  IF EXISTS countries;
CREATE TABLE IF NOT EXISTS countries (
   country_iso_code    VARCHAR (5)  NOT NULL,
   country_name       VARCHAR( 50 ) NOT NULL,
   PRIMARY KEY ( country_iso_code )
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*
 * Postal Code Table
 */
DROP TABLE IF EXISTS zipcodes;
CREATE TABLE IF NOT EXISTS zipcodes (
   zipcode_id             INT(11)  NOT NULL,
   zipcode               VARCHAR(10   ) NOT NULL,
   city                     VARCHAR(50) NOT NULL,
   country_iso_code    VARCHAR(5) NOT NULL,
   PRIMARY KEY ( zipcode_id ),
   CONSTRAINT FOREIGN KEY ( country_iso_code ) REFERENCES countries ( country_iso_code )
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*
 *  Address Table
 */
DROP TABLE IF EXISTS addresses;
CREATE TABLE IF NOT EXISTS addresses (
   address_id          INT(11) NOT NULL,
   streetname       VARCHAR(250) NOT NULL,
   housenumber    INT(11) NOT NULL,
   appartment       VARCHAR(1) DEFAULT NULL,
   box                   INT(11) DEFAULT NULL,
   zipcode_id         INT(11) NOT NULL,
   info                  VARCHAR(250) DEFAULT NULL,
   PRIMARY KEY ( address_id ),
   CONSTRAINT FOREIGN KEY ( zipcode_id ) REFERENCES zipcodes ( zipcode_id)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*
 * Person table
 */
DROP TABLE IF EXISTS person;
CREATE TABLE IF NOT EXISTS person (
   person_id    INT NOT NULL,
   firstname    VARCHAR(50) NOT NULL,
   lastname    VARCHAR(50) NOT NULL,
   PRIMARY KEY ( person_id )
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*
 * Identities Table
 * This table will be populated with data from the
 * BEID Card API
 */
DROP TABLE IF EXISTS identities;
CREATE TABLE IF NOT EXISTS identities (
   identity_id          INT PRIMARY KEY REFERENCES person (person_id),
   nationalnumber   INT(11) NOT NULL,
   nationality          VARCHAR(25) DEFAULT NULL,
   dateofbirth          DATE DEFAULT NULL,
   placeofbirth       VARCHAR(50) DEFAULT NULL,
   picture             BLOB,
   maritalstatus       VARCHAR(50) DEFAULT NULL,
   lang                VARCHAR(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*
 * Customer Table
 */
DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
      `customer_id`    INT(11) NOT NULL,
      `contact_id`       INT(11) NOT NULL,
      `address_id`       INT(11) DEFAULT NULL,
      `partner_id`       INT(11) DEFAULT NULL,
      PRIMARY KEY ( `customer_id` ),
      CONSTRAINT FOREIGN KEY ( `contact_id` ) REFERENCES person ( `person_id` ) ON DELETE CASCADE,
      CONSTRAINT FOREIGN KEY ( `partner_id` ) REFERENCES person ( `person_id` ) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*
 * Users Table
 */
DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
   `id`                   MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
   `contact_id`       INT(11) NOT NULL,
   `username`       VARCHAR(100) NOT NULL,
   `password`         VARCHAR(80) NOT NULL,
   `created_on`      INT(11) UNSIGNED NOT NULL,
   `last_login`         INT(11) UNSIGNED NOT NULL,
   `active`             TINYINT(1) UNSIGNED DEFAULT NULL,
   PRIMARY KEY ( `id`),
   CONSTRAINT FOREIGN KEY ( `contact_id` ) REFERENCES person ( `person_id` ) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*
 * MediaType Tables
 */
DROP TABLE IF EXISTS `mediatype`;
CREATE TABLE IF NOT EXISTS `mediatype` (
   id             INT(3) PRIMARY KEY NOT NULL,
   description   VARCHAR(20) NOT NULL   
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*
 * ContactMedia Table
 */
DROP TABLE IF EXISTS `contactmedia`;
CREATE TABLE IF NOT EXISTS `contactmedia` (
   contact_id      INT(3) PRIMARY KEY REFERENCES person (person_id),
   mediatype_id    INT(3) NOT NULL,
   content         VARCHAR(20) NOT NULL,
   CONSTRAINT FOREIGN KEY ( mediatype_id ) REFERENCES mediatype ( `id` )
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Related topics
E-Cards with PHP & MySQL
HTTP AUTH with PHP and mySQL
mysql table keyword search
Working with Excel, PHP & MySQL. Any Ideas
SELECTing from MySQL with PHP
Can't create mySQL table
Reading MySQL values in reverse
Export and Import Mysql Table
how to selecting this from a mysql table ?
Developing a Login System with PHP and MySQL
PHP/MySQL challenge, take it if you dare
Data Mining From tables and then insert into MySQL
a new mysql table...
Subtrat values to a mysql table script
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.