SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    FreeBSD The Power to Serve silver trophy pippo's Avatar
    Join Date
    Jul 2001
    Location
    Italy
    Posts
    4,514
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need some table design hints

    Hi,

    I'm in the process of redesign my tables.
    I'm using mysql.

    I have some doubts:

    I'm going to create a people table,
    but I have no idea which one choose:

    Table A
    Code:
     CREATE TABLE people(
     id INT NOT NULL PRIMARY KEY,
     first_name VARCHAR(255) NOT NULL,
     last_name VARCHAR(255) NOT NULL,
     email VARCHAR(255) NOT NULL
     );

    Table B
    Code:
     CREATE TABLE people(
     first_name VARCHAR(255) NOT NULL,
     last_name VARCHAR(255) NOT NULL,
     email VARCHAR(255) NOT NULL,
     PRIMARY KEY(first_name, last_name)
     );
    The url path layout to access people informations will be:

    for Table A:
    /people/id

    for Table B:
    /people/firstname_lastname

    My doubt is which table is better to choose.

    Another doubt.
    As you can see I used VARCHAR(255).
    That was because I didn't want to have eventual future limitations.
    I could use VARCHAR(100) for example, but I think that there is no advantage of limiting the size.
    Is this true ?!?

    Thanx!

    Mr Andrea
    Former Hosting Team Advisor
    Former Advisor of '03

  2. #2
    morphine for a wooden leg randem's Avatar
    Join Date
    Jun 2002
    Location
    .chicago.il.us
    Posts
    957
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Table A is the better choice.

    1) because there is always the potential to find someone with the same first and last name, and...

    2) because searching by number is much faster than searching by text. you'll only have to look up the name once, then you can use the id field

    Regarding VARCHAR(100) vs VARCHAR(255), I don't have any concrete evidence, but I believe the VARCHAR(100) would be more efficient in use.

  3. #3
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    randem's right... use a separate id instead since common first and last names are not uncommon (pun intended )

    as for your field size... using VARCHAR(100) would be a better idea imho... unless you're expecting really long names, go with the max. length you think possible and then add 50% to that (for more leeway)... this would save you space (obviously!) as well as increase efficiency since larger data is obviously slower to read (like randem said... again )

  4. #4
    FreeBSD The Power to Serve silver trophy pippo's Avatar
    Join Date
    Jul 2001
    Location
    Italy
    Posts
    4,514
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both!

    About VARCHAR(255) mysql allocate only 1 byte + the data inside that field.

    My thought was that if the name is 10 chars long,
    using VARCHAR(100) or VARCHAR(255) it will allocate the same space.

    TX!
    Mr Andrea
    Former Hosting Team Advisor
    Former Advisor of '03

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)

    Natural or Surrogate Key

    yup, you're right, pippo, there is no difference in size or speed between varchar(100) and varchar(255)

    as for your design, table A is much better, not only because of the uniqueness factor, but also when you want to relate another table

    for example, let's say that along with your person table, you also want to have a table to list a person's email ids

    since this is a one-to-many relationship, the email table will have to have a foreign key which points back to the person table, linking each email with the person it belongs to

    if you use lastname and firstname as the primary key in the person table, you will also have to use it in the email table, and then look at how much space that would take up! never mind squeezing a byte or two out of your varchars, think about Constantine Thistlebottom being repeated eleven times because he has eleven emails!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are little/no performance implications with an unindexed VARCHAR( 255 ) vs. VARCHAR( 100 ) in theory.

    However, your data structures should match the business requirements. This way the data structure themselves becomes a data dictionary for others to model their applications (report generation and the like). If you have your email address VARCHAR( 255 ) and another application reads/writes to it, they may inadvertantly not adhere to whatever limit you developed in your application, which will break your first application. You would need strict documentation stating "Yeah we have it set to 255 but it MUST be kept under X characters" which every application must remember to adhere to. Defining it VARCHAR( X ) is self-documenting.

    Also, indexes may be needlessly wide on a VARCHAR( 255 ) regardless of whether or not you have 255 characters in there. It depends on your RDBMS and how they physically store their indexes so it would be wise to see if inefficiencies exist in a VARCHAR( 255 ) index over a VARCHAR( X ).

    Further RDBMS-specific indexing may impose a limit on the number of bytes an index can hold. So if you define your table with username and password of 255 characters and attempt to place an index on those two (since typically authentication is done with username and password) you will not be able to create it if your max index length (as specified by the RDBMS) is 300 bytes.

  7. #7
    FreeBSD The Power to Serve silver trophy pippo's Avatar
    Join Date
    Jul 2001
    Location
    Italy
    Posts
    4,514
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937,
    I didn't thought about that.
    In fact I will have to use people id as a "foreign key" for article table.

    MattR, thanx for your clarifications.

    TX a lot,
    Mr Andrea
    Former Hosting Team Advisor
    Former Advisor of '03

  8. #8
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Constantine Thistlebottom? Poor girl.....!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


Bookmarks

Posting Permissions

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