Database design

sidsel

I'm trying to design a database table optimzed for speed that will hold a lot of rows. I will select using "contributor_id" and "approved"

My idea of making it would be something like this:

CREATE TABLE comments (
id int(11) NOT NULL auto_increment,
timestamp int(14) DEFAULT '0' NOT NULL,
contributor_id varchar(10),
INDEX contributor_id (contributor_id),
name varchar(30),
email varchar(50),
ip varchar(15) NOT NULL,
comment varchar(250) NOT NULL,
approved tinyint(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);

But do I need the column "id"? Couldn't I just make the column "contributor_id" the primary key and still use a index?

Still a newb so any input is appreciated. Thanks.

ihenman

If the contributor_id is always unique then sure you could use that as your index. But if it's not and you could eventually expand the DB to be relational then i'd reccommend adding another column

id, int, and autoincrement...then use this as your index.

This ensures that every row has something unique and therfore you can select one specific row easliy if ever required.

drawmack

Originally posted by sidsel
I'm trying to design a database table optimzed for speed that will hold a lot of rows. I will select using "contributor_id" and "approved"

My idea of making it would be something like this:

CREATE TABLE comments (
id int(11) NOT NULL auto_increment,
timestamp int(14) DEFAULT '0' NOT NULL,
contributor_id varchar(10),
INDEX contributor_id (contributor_id),
name varchar(30),
email varchar(50),
ip varchar(15) NOT NULL,
comment varchar(250) NOT NULL,
approved tinyint(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (id)
);

1) index every field that's going to be used in a where or orderby clause - I've said that about 50 times this week.

But do I need the column "id"? Couldn't I just make the column "contributor_id" the primary key and still use a index?

Still a newb so any input is appreciated. Thanks.

2) you need a primary key, so yes keep the id. If you make contributor_id the primary key then each contributor can only contribute one piece of news which seems counter productive.