Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jan 2009
    Posts
    22
    Thanks
    2
    Thanked 0 Times in 0 Posts

    INDEX with duplicates

    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?

    Code:
    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;

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    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
    Code:
        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:
    Code:
    	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??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Jan 2009
    Posts
    22
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Code:
        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:
    Code:
    	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:

    Code:
    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?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    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
    Code:
    	FOREIGN KEY( `rating_reply_id` ) REFERENCES `replies_text`( `reply_text_id` ),
    in RATINGS is redundant.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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