Database creation: Need pro eyeballs/advice

Databases
1

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).

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/[B]1H2wS4z9ks[/B]

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

no, the pk should be id only

no, it should actually be UNIQUE

3

Hi r937, thanks so much for the help! I really appreciate it. :slight_smile:

Based on your reply, here is what I got:


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

No problem creating the table! Thanks a billion r937, I owe you one. :slight_smile:

Cheers,
Micky