[eluser]Twisted1919[/eluser]
Hi , i've just started to learn about Innodb features in Mysql and i would like to move on and using it in my projects (still keep MyISAM for doing full searches).
My question is pretty simple , i am a bit confused about the indexes .
Let's say we have a table for users :
Code: CREATE TABLE IF NOT EXISTS `cp`.`user` (
`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(45) NULL ,
`password` VARCHAR(45) NULL ,
`email` VARCHAR(45) NULL ,
`status` ENUM('active','inactive') NULL ,
PRIMARY KEY (`user_id`) )
ENGINE = InnoDB;
Table for users profile(with one-to-one rel)
Code: CREATE TABLE IF NOT EXISTS `cp`.`user_profile` (
`user_id` INT UNSIGNED NOT NULL ,
`description` VARCHAR(250) NULL ,
`birth_date` DATE NULL ,
`sex` VARCHAR(45) NULL ,
`interest` VARCHAR(250) NULL ,
INDEX `fk_user_profile_user1` (`user_id` ASC) ,
CONSTRAINT `fk_user_profile_user1`
FOREIGN KEY (`user_id` )
REFERENCES `cp`.`user` (`user_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Table for users images (with one-to-many rel)
Code: CREATE TABLE IF NOT EXISTS `cp`.`user_images` (
`image_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`user_id` INT(11) UNSIGNED NOT NULL ,
`name` VARCHAR(45) NOT NULL ,
`type` ENUM('primary','secondary') NULL ,
`sort_order` INT UNSIGNED NOT NULL DEFAULT 1 ,
INDEX `fk_image_user` (`user_id` ASC) ,
PRIMARY KEY (`image_id`) ,
CONSTRAINT `fk_image_user`
FOREIGN KEY (`user_id` )
REFERENCES `cp`.`user` (`user_id` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Now , in my last 2 tables , user_id is a foreign key pointing to users table .
There is a need to "extra-specify" user_id as a INDEX in the second two tables with CREATE INDEX index_name ON table ?
I've designed the structure with Mysql Workbench which is a new tool for me and i hope i get this right .
Thanks .
[eluser]Đaяк Đaηтє[/eluser]
If you want to build relationships, you must declare the userid field as primary key and this will become automatically index, if you do not declare the field as primary key then there will be no real relationship between tables:
The relationship between the first two tables is useless, you can store the information into a table and still maintain the integrity of the tables:
Code: CREATE TABLE IF NOT EXISTS `cp`.`user` (
`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(45) NULL ,
`password` VARCHAR(45) NULL ,
`email` VARCHAR(45) NULL ,
`status` ENUM('active','inactive') NULL ,
`description` VARCHAR(250) NULL ,
`birth_date` DATE NULL ,
`sex` VARCHAR(45) NULL ,
`interest` VARCHAR(250) NULL ,
PRIMARY KEY (`user_id`) )
ENGINE = InnoDB;
An then create the images table:
Code: CREATE TABLE IF NOT EXISTS `cp`.`user_images` (
`image_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`user_id` INT(11) UNSIGNED NOT NULL ,
`name` VARCHAR(45) NOT NULL ,
`type` ENUM('primary','secondary') NULL ,
`sort_order` INT UNSIGNED NOT NULL DEFAULT 1 ,
INDEX `fk_image_user` (`user_id` ASC) ,
PRIMARY KEY (`image_id`,`user_id`) ,
CONSTRAINT `fk_image_user`
FOREIGN KEY (`user_id` )
REFERENCES `cp`.`user` (`user_id` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB;
CASCADE = Update or delete all child records when parent record is updated or deleted.
SET NULL = Set to NULL all child records when parent record is deleted or updated, including the parent record.
NO ACTION = Does not allow deleting a parent record while there is a child record. If required delete the parent record shall be ensured that there are no child records, to maintain the integrity of the database.
Greetings...
[eluser]Twisted1919[/eluser]
Thank you very much for your time , you really helped me to understand this .
It will require more time to understand this engine , but i guess it will worth 
[eluser]Twisted1919[/eluser]
Of , not to forget .
The second table that you say is useless , i used because the first table containing the userid/passwd/email contains the data that i search for in the most of the cases , and on the other hand , description/sex/interest(second table) is a table that i require data from in just a few places , therefore i thought that acting like this , will maintain my primary table more lightweight so the selecting queries in this table would be faster when reaching a high number of users .
Is this ideea right , wrong or it can be accepted ?
[eluser]Đaяк Đaηтє[/eluser]
In my opinion, I think, is not an optimal structure. With a small volume of simultaneous connections would be imperceptible to the fact that in some cases need a INNER JOIN between the first two tables in your schema to obtain full details of the registered user. If you request only make a login form, for example, you only will do a SELECT with the fields necessary to do that, for example:
Code: SELECT user_id, username, password FROM users WHERE username = "SomeValueSubmittedByGetOrPost" AND password = "SomeValueSubmittedByGetOrPost";
The query above will be more optimal instead of a query like the one shown below:
Code: SELECT * FROM users WHERE username = "SomeValueSubmittedByGetOrPost" AND password = "SomeValueSubmittedByGetOrPost";
Therefore, it is not necessary to store user data in two different tables if the relationship between tables is one to one, at least not in this case, the query optimization depend heavily on how elaborate such SQL queries, and not in the table structure itself (Referring to this particular case).
There are cases in which, you will need two tables to maintain normalized your database, such a relationship between tables: mobiles and users, because a user probably has one or more mobiles.
In other cases require one-to-one, for example a user on the table employee who is employed by any department but it also is the manager of that department, this is an attribute that no more of their colleagues in the department shares Do I explain my point?
[eluser]Twisted1919[/eluser]
Yes , it's pretty obvious what you say . I know about relationships between tables(not an expert though) and i still learn from where i can find resources .
Thank you for your time , you've been very helpfull
|
