...

View Full Version : INDEX with duplicates



DrWily
09-20-2011, 03:47 PM
I'm making a rating system. I expect to have lots of ratings so I want the lookups to be fast because they'll be all over the site. Since there will be multiple ratings for each reply, each person they're for, and from each rater, there will be no unique column in the table. So I made the rating_meta_id column which will just make a useless id for each rating. I also made a non-unique INDEX for the rating_for_id column since that's the column that will be used the most. Which is better? Is there a better way to go about this?


CREATE TABLE `ratings`
(
`rating_meta_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`rating_category_id` TINYINT UNSIGNED NOT NULL,
`rating_score` TINYINT NOT NULL,
`rating_date` DATETIME NOT NULL,
`rating_reply_id` INT UNSIGNED NOT NULL,
`rating_for_id` INT UNSIGNED NOT NULL,
`rating_by_id` INT UNSIGNED NOT NULL,
PRIMARY KEY( `rating_meta_id` ),
INDEX `RATING_FOR_ID`( `rating_for_id` ),
FOREIGN KEY( `rating_category_id` ) REFERENCES `rating_categories`( `rating_id` ),
FOREIGN KEY( `rating_reply_id` ) REFERENCES `replies`( `reply_id` ),
FOREIGN KEY( `rating_reply_id` ) REFERENCES `replies_text`( `reply_text_id` ),
FOREIGN KEY( `rating_for_id` ) REFERENCES `users`( `user_id` ),
FOREIGN KEY( `rating_by_id` ) REFERENCES `users`( `user_id` )
)
ENGINE = INNODB;

Old Pedant
09-20-2011, 09:41 PM
Kill the rating_meta_id column if it's not used for anything.

Nobody said you *must* have a primary key on every table, you know.

In fact, if no other table has a REFERENCES clause *TO* this table, there's no reason at all for one.

But, in any case, you *can* use a composite key as the primary key (or even as any kind of index).

I don't really think you need/want it here, by you could do

PRIMARY KEY (rating_reply_id,rating_for_id,rating_by_id)
as I assume you would only want one rating of each reply by each person.

Hmmm...how come you need both rating_reply_id *AND* rating_for_id???

Isn't each reply_id uniquely posted by one person?

For that matter, these two are a bad sign:

FOREIGN KEY( `rating_reply_id` ) REFERENCES `replies`( `reply_id` ),
FOREIGN KEY( `rating_reply_id` ) REFERENCES `replies_text`( `reply_text_id` ),

Makes me believe your tables are not properly normalized.

Maybe you want to show your DB design, all tables??

DrWily
09-20-2011, 11:14 PM
PRIMARY KEY (rating_reply_id,rating_for_id,rating_by_id)
as I assume you would only want one rating of each reply by each person.

Hmmm...how come you need both rating_reply_id *AND* rating_for_id???

Isn't each reply_id uniquely posted by one person?

For that matter, these two are a bad sign:

FOREIGN KEY( `rating_reply_id` ) REFERENCES `replies`( `reply_id` ),
FOREIGN KEY( `rating_reply_id` ) REFERENCES `replies_text`( `reply_text_id` ),


rating_meta_id killed. Yes each person can rate a reply only once so that might be what I want. I added rating_for_id (the user_id of the person being rated) to the table because I was under the impression the tables were sorted according to the index in which case I would want them ordered by the rating_for_id so the rating_score could be SUMed quickest for each user_id because that would be what is usually done with the ratings and each user would have more ratings than any one reply.

The two FOREIGN KEYs you mentioned reference those two tables because the replies tables are split up, one for the reply_id, replier, etc, and one for the reply_id and TEXT only so the fixed size data is separate from the dynamic sized. I'm thinking there should only be one reference now, though. Here's my entire DB so far:


DROP TABLE IF EXISTS ratings;
DROP TABLE IF EXISTS rating_categories;
DROP TABLE IF EXISTS replies_text;
DROP TABLE IF EXISTS replies;
DROP TABLE IF EXISTS topics;
DROP TABLE IF EXISTS topic_categories;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS user_levels;

CREATE TABLE `user_levels`
(
`level_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`level_name` VARCHAR( 32 ) NOT NULL,
PRIMARY KEY( `level_id` ),
UNIQUE INDEX `LEVEL_NAME_UNIQUE`( `level_name` )
)
ENGINE = INNODB;

INSERT INTO `user_levels`( `level_name` ) VALUES( "Admin" );
INSERT INTO `user_levels`( `level_name` ) VALUES( "Moderator" );
INSERT INTO `user_levels`( `level_name` ) VALUES( "Member" );
INSERT INTO `user_levels`( `level_name` ) VALUES( "Banned" );

CREATE TABLE `users`
(
`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR( 32 ) NOT NULL,
`user_password` VARCHAR( 40 ) NOT NULL,
`user_email` VARCHAR( 128 ) NOT NULL,
`user_date` DATETIME NOT NULL,
`user_level_id` TINYINT UNSIGNED NOT NULL,
`user_suspension` DATETIME NOT NULL,
`user_rating_multiplier` DECIMAL( 2, 1 ) UNSIGNED DEFAULT 1.0 NOT NULL,
PRIMARY KEY( `user_id` ),
UNIQUE INDEX `USER_NAME_UNIQUE`( `user_name` ),
FOREIGN KEY( `user_level_id` ) REFERENCES `user_levels`( `level_id` )
)
ENGINE = INNODB;

INSERT INTO `users`( `user_name`, `user_level_id`, `user_date` ) VALUES( "Wily", 1, NOW() );
UPDATE `users` SET `user_level_id` = 4 WHERE `user_id` = 1;

CREATE TABLE `topic_categories`
(
`category_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`category_name` VARCHAR( 128 ) NOT NULL,
`category_description` VARCHAR( 128 ) NOT NULL,
PRIMARY KEY( `category_id` ),
UNIQUE INDEX `CATEGORY_NAME_UNIQUE`( `category_name` )
)
ENGINE = INNODB;

CREATE TABLE `topics`
(
`topic_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`topic_subject` VARCHAR( 64 ) NOT NULL,
`topic_date` DATETIME NOT NULL,
`topic_category_id` TINYINT UNSIGNED NOT NULL,
`topic_author_id` INT UNSIGNED NOT NULL,
PRIMARY KEY( `topic_id` ),
FOREIGN KEY( `topic_category_id` ) REFERENCES `topic_categories`( `category_id` ),
FOREIGN KEY( `topic_author_id` ) REFERENCES `users`( `user_id` )
)
ENGINE = INNODB;

CREATE TABLE `replies`
(
`reply_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`reply_date` DATETIME NOT NULL,
`reply_topic_id` INT UNSIGNED NOT NULL,
`reply_author_id` INT UNSIGNED NOT NULL,
PRIMARY KEY( `reply_id` ),
FOREIGN KEY( `reply_topic_id` ) REFERENCES `topics`( `topic_id` ),
FOREIGN KEY( `reply_author_id` ) REFERENCES `users`( `user_id` )
)
ENGINE = INNODB;

CREATE TABLE `replies_text`
(
`reply_text_id` INT UNSIGNED NOT NULL,
`reply_text` TEXT NOT NULL,
PRIMARY KEY( `reply_text_id` ),
FOREIGN KEY( `reply_text_id` ) REFERENCES `replies`( `reply_id` )
)
ENGINE = INNODB;

CREATE TABLE `rating_categories`
(
`rating_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`rating_name` VARCHAR( 32 ) NOT NULL,
PRIMARY KEY( `rating_id` )
)
ENGINE = INNODB;

CREATE TABLE `ratings`
(
`rating_category_id` TINYINT UNSIGNED NOT NULL,
`rating_score` TINYINT NOT NULL,
`rating_date` DATETIME NOT NULL,
`rating_reply_id` INT UNSIGNED NOT NULL,
`rating_for_id` INT UNSIGNED NOT NULL,
`rating_by_id` INT UNSIGNED NOT NULL,
INDEX `RATING_FOR_ID`( `rating_for_id` ),
FOREIGN KEY( `rating_category_id` ) REFERENCES `rating_categories`( `rating_id` ),
FOREIGN KEY( `rating_reply_id` ) REFERENCES `replies`( `reply_id` ),
FOREIGN KEY( `rating_reply_id` ) REFERENCES `replies_text`( `reply_text_id` ),
FOREIGN KEY( `rating_for_id` ) REFERENCES `users`( `user_id` ),
FOREIGN KEY( `rating_by_id` ) REFERENCES `users`( `user_id` )
)
ENGINE = INNODB;


So, where the rating_reply_id references the two reply tables, I take it only referencing the replies table is sufficient, the one that auto_increments? I have a similar problem with another thing I want to do which is separate the user authentication data from the user profile type data. Which user id would be referenced to in that situation?

So with this setup, when a new reply is made, I would update the reply_text_id in the replies_text table with the auto_incremented reply_id from the replies table immediately after the auto_incremented reply_id is set and that is sufficient?

Old Pedant
09-21-2011, 12:15 AM
Adding a FOREIGN KEY (a REFERENCES) does *NOT* have any impact at all on whether a column is indexed, in either that referenced table or in the referencing table.

I think you are making a mistake having both REPLIES and REPLIES_TEXT tables. It looks to me like that's a one-to-one relationship, no?? If not, then okay, but then still the


FOREIGN KEY( `rating_reply_id` ) REFERENCES `replies_text`( `reply_text_id` ),

in RATINGS is redundant.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum