Int and join versus char (2)

Results 1 to 3 of 3

Thread: Int and join versus char (2)

  1. #1
    Join Date
    Dec 1969

    Default Int and join versus char (2)

    We&#039;re setting up a new e-commerce site using SQL 7. We want to normalize using 3rd normal form, but wonder if we&#039;re getting a little "too fancy." Example: For the state field, in our shoppers table, we have StateID int; but, we would then have to use a (inner/left) join when reporting on this table and outputting the state to the user. In the past we used char(2) and the abbreviation for the state.<BR><BR>So my question is this: From a performance/maintenance standpoint, should we use char(2) and no join or int and join on the state table? (Same question applie to country and credit card type.)<BR><BR>Thanks

  2. #2
    Join Date
    Dec 1969

    Default RE: Int and join versus char (2)

    It&#039;s Down to personal preference!<BR><BR>I use the char 2 in display But for a dropdown for (Dumb users) use the state name with a value of the char 2<BR><BR>Country I&#039;d always use the Full thing .. Credit cards I don&#039;t use so cannot comment

  3. #3
    Join Date
    Dec 1969

    Default You are misinterpreting...

    ...the requirements of Normalization in general and 3rd normal form in particular!<BR><BR>There is *NOTHING* that says you MUST use an integer as the basis for a primary key/foreign key relationship. So long as you use *SOMETHING* that is guaranteed to be unique, that&#039;s a perfectly valid primary key.<BR><BR>It&#039;s just plain nutso to use an integer when the state abbreviations are natural primary keys!<BR><BR>Performance-wise: If it&#039;s a CHAR(2) field, then the performance should be essentially identical to any integer type. Anything up to CHAR(4) can be shoved into a CPU register for a comparison, so a decent optimizer should do that. [Varchar is another situation of course; it will always take slightly longer to compare varchar than integer...or char.]<BR><BR>

Posting Permissions

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