Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Problem on adding foreign key

    I have 2 table customer and Sales Person

    When create record for customer, we will let user to select sales person, but not compulsory, user can let it be empty.

    Now i facing problem when i try to add foreign key from customer to sales person table. Anyone have any idea?

    Thank in advance.

    Below is my table structure.

    CREATE TABLE `customer` (
    `cust_code` varchar(10) NOT NULL default '',
    `Name` varchar(100) NOT NULL default '',
    `PIC` varchar(80) NOT NULL default '',
    `PHONE` varchar(20) default NULL,
    `MOBILE_PHONE` varchar(12) NOT NULL default '' COMMENT 'Mobile Phone',
    `FAX` varchar(12) NOT NULL default '',
    `EMAIL` varchar(60) NOT NULL default '',
    `CONTACT_PERSON` varchar(80) NOT NULL default '',
    `CONTACT_POST` varchar(60) NOT NULL default '',
    `ADDRESS1` text NOT NULL,
    `postcode` varchar(10) NOT NULL default '',
    `CITY` varchar(5) NOT NULL default '',
    `STATE` varchar(3) NOT NULL default '',
    `COUNTRY` varchar(3) NOT NULL default '',
    `SP_CODE` varchar(10) NOT NULL default '',
    )

    CREATE TABLE `sales_person` (
    `sp_code` varchar(10) NOT NULL default '',
    `sp_name` varchar(100) NOT NULL default '',
    `PHONE` varchar(20) default NULL,
    `ADDRESS1` varchar(100) NOT NULL default '',
    `postcode` varchar(10) NOT NULL default '',
    `CITY` varchar(5) NOT NULL default '',
    `STATE` varchar(3) NOT NULL default '',
    `COUNTRY` varchar(3) NOT NULL default '',
    `EMAIL` varchar(60) NOT NULL default '',
    )

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what precisely is your problem

    trying to define a foreign key
    OR
    trying to work out why you cant link the two tables

    if you want to define a foreign key then you need to be using INNODB tables, and a more recent version of MySQL, forget when foreign keys came in, think its around V5.

    However in your table definition Id suggest you have a few anomolies
    first off you force the SalesRep code in the customer table be non null, and set to "" if not provided, this isnt the same as NULL. If your customers cant assign a salesperson, then Id suggest it should be set to NULL

    you define different field lengths for telephone/fax/mobile numbers, all telephone numbers are of the same size, depending on how you define the international code but effectively its +xxx nnnnnnnnnnn, where + indicates an interantional call, xxx the country and nnn is the number, US numbers can break down the customer number into 3 sub components of 10 characters (3, 3 & 4 - for area(3) exchange(3) number(4)). Generall I reckon on allwoing 15 characters (excluding punctuation) most UK numbers are around 11 characters (I suspect the interantional standard is probably 10 characters & the UK prepeneds a zero for legacy reasons, leaving 4 characters + & the 3 digit country code). Arguable if you have the country then you dont need to add the international dialing code, except Canada & the US (and a few other countries use the same code 001

    I find it odd you are using a character defintion to store the salesrep code & Customer Code, normally Id expect to use a system generated number implemented as an autonumber column. Incidentally Im not sure you can define a varchar column to be a foreign key

    Varchar isnt necesarily appropriate for all fields, especially if you know the maximum field length, or there isnt much varaition. It s very usefull for massively varying elements, however using varchar does limit the indexing choices (I cant remember precisley but Varchar may nto be compatible with FULLTEXT)

    your address definition is odd, Im not sure you can coerce the US style of addresses into an international model - it will certainly fail if you try to use the db in the UK (eg a city of 5 chars, a state of 3 characters)

    Id suggest that you ensure consistency in naming columns (either all capitals, all lower case or all mixed) eg you use SP_CODE in customer & sp_code in salesperson. Personally I prefer to use DT prepended to table names (but many people frown at that, Id use SpCode in place of SP_CODE/sp_code, if using an autonumber column Id generally use ID as in SalesRepID

    incidentally if you are storing country then Id suggest using the ISO 2 character country codes. do a google on the ISO country codes for details)

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2004
    Posts
    57
    Thanks for your suggestion.
    I am using MySql 5, from your answer, i get my solution is to set sales person code to null if customer cant assign a customer.

    I have some other question:
    1. what should i set for state and city code if to use for international?
    2. For sp code and customer code, we do consider to use auto increament field, however, there is some comment that if use auto increament field as key code, we will facing problem when need to do data Analysis cross database, what is your opinion?

    Thank you.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by GongXi
    ....I have some other question:
    1. what should i set for state and city code if to use for international?
    2. For sp code and customer code, we do consider to use auto increament field, however, there is some comment that if use auto increament field as key code, we will facing problem when need to do data Analysis cross database, what is your opinion?

    Thank you.
    there is no international standard for addresses. Being UK based, where addresses can be upto 5 line long, and a postcode can be 10 Characters EG: XX11 22YY, where XX is the code for the areas main sorting office, 11 is the post town minor sorting office, 22 is the delivery round & YY is the building or building group. A UK postcode identifes a physical geographical area, it could be a single builiding for large commercail users, or it could be 10..15 residential addrresses. A single building may have multiple postcodes, even if its the same company. The closest to state in the UK is County, but there is no valid prefix. The City/Town isn't neccesarily the same as the postcode. Where I Live you have an address whihc is the county of manchester, but the postcode prefix is WA indicating that the major sorting office is in Warrington, some 25 miles away, in a different county.

    British addresses flow, they are not structured as US addresses seem to be, ie they may be 2 rows, they may be 5 rows. There are two main subtypes: House/Building Name, StreetName, Postal Area (optional), City, State. PostCode AND The House/Building No + StreetName, Postal Area (optional), City, State. PostCode. The city may appear on lines 2,3 or 4, the state/county may or may not appear, optionally a country may appear as the last line (eg England, Wales, Scotland... if the writer is being especailly nationalistics or perverse

    post office can deliver based on the first line of the address (ie the housename, hor the housenumber AND postcode, OR the full address [ including the house name/number, street, settlement post town county]).

    There is no reason why you cannot use the the US system, however if you want to send to the UK you need to have a method which supports additional lines of address, and print out on labels / invocies in the correct sequence. So I tend to use 5 x varchar(50) + Char(10) for PostCodes, and rely on the customer to provide the sequence OR do a lookup on the postcode. If the customer supplies a postcode and House name/number its possible to do a lookup and retrieve the rest of the addrerss.


    I dont understand you issue regarding the choice of an alpha spRep code as casuing problems if you want to analyse data. The only valid reason in my books is if its to be compatible with an existing application elsewhere, if its only used in this application then use an autonumber. you can leverage the power of SQL to pull through any codes/names form the salesrep table.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2004
    Posts
    57
    Thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •