SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database creation: Need pro eyeballs/advice

    Hi,

    I just wanted to get a little feedback on my table setup here (I am still pretty nooby when it comes to DB creation).

    Code:
    CREATE TABLE grid_data(
    	id INT NOT NULL AUTO_INCREMENT,
    	sid VARCHAR(10) NOT NULL,
    	data TEXT NOT NULL,
    	tm timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    	PRIMARY KEY(id, sid),
    	INDEX(sid)
    )
    Sid will be a 10 character "secret" id... This will be what I use in the URI:

    http://foo.com/app/1H2wS4z9ks

    The data will probably be no longer than 255 chars, but I opted to go with TEXT just in case I need a little extra space.

    The rest should be pretty self explanatory.

    I plan on just creating new rows to the table and will never alter existing rows.

    Q: Is the composite primary key necessary? I mean, I can see adding a primary key for id, but what about "sid"? I am not sure yet, but I think I will be querying the DB using the SID (and not ID).

    Q: Is the index overkill?

    In general, I am just looking for some feedback; Please keep in mind, I am still pretty fresh for when it comes to DB creation.

    Thanks!
    Micky

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mhulse View Post
    Q: Is the composite primary key necessary?
    no, the pk should be id only

    Quote Originally Posted by mhulse View Post
    Q: Is the index overkill?
    no, it should actually be UNIQUE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937, thanks so much for the help! I really appreciate it.

    Based on your reply, here is what I got:

    Code:
    CREATE TABLE grid_data(
    	id INT NOT NULL AUTO_INCREMENT,
    	sid VARCHAR(10) NOT NULL,
    	data TEXT NOT NULL,
    	tm timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    	PRIMARY KEY(id),
    	UNIQUE INDEX(sid)
    )
    Unfortunately, I can't test the code right now, but I will be back later with my results.

    Thanks again for the pro help! I really appreciate you taking the time to help me fine-tune my code.

    Have a nice day,

    Cheers,
    Micky

  4. #4
    .* draziW tnioPetiS *. bronze trophy
    Join Date
    Jun 2004
    Location
    "Then I figure the most good good guy will win."
    Posts
    1,666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem creating the table! Thanks a billion r937, I owe you one.

    Cheers,
    Micky


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
  •