I wanted to know what is the better approach (performance wise) when working with Yii and mysql tables:
basically, what i'm asking is: Is there any performance degradation when a single table has many relations since Yii will fetch all the corresponding tables into the object when you do a query?
A more general question is: what is good practice and smarter approach when dealing with 1 vs multiple tables and foreign keys (again performance..)
If performance was the only criteria in your design strategy, you would choose 1. A very bad choice, that would give you a lot of problems in a later stage of your problems.
Databases should be designed with normalization in mind, so without any doubt, you must choose option 2!
Update
Example of normalized and not normalized: suppose we have a table with dvd rentals, and want to keep track of who rented the dvd:
flat table:
CREATE TABLE DVD (
DvdId INT NOT NULL AUTO_INCREMENT,
DvdTitle varchar(64),
Rental1 varchar(64),
Rental2 varchar(64),
Rental3 varchar(64),
Primary Key(DvdId)
);
There is room for 3 rental entries, after that there is trouble. Beside that, when there is a extra field needed for the phone number of the customers, 3 extra fields must be made.
First step normalization:
CREATE TABLE DVD (
DvdId INT NOT NULL AUTO_INCREMENT,
DvdTitle varchar(64),
Primary Key(DvdId)
);
CREATE TABLE Rentals (
RentalId INT NOT NULL AUTO_INCREMENT,
DvdId INT NOT NULL,
CustomerName varchar(64),
RentalDate DateTime,
Primary Key(RentalId)
);
This is better, but still not fully normalized. What if a customer rents the same dvd twice. Then there would be a double customer entry in the rentals table.
Final:
CREATE TABLE DVD (
DvdId INT NOT NULL AUTO_INCREMENT,
DvdTitle varchar(64),
Primary Key(DvdId)
);
CREATE TABLE Rentals (
RentalId INT NOT NULL AUTO_INCREMENT,
DvdId INT NOT NULL,
CustomerId INT NOT NULL,
RentalDate DateTime,
Primary Key(RentalId)
);
CREATE TABLE Customers (
CustomerId INT NOT NULL AUTO_INCREMENT,
CustomerName varchar(64),
Primary Key(CustomerId)
);
The better option is to design your database properly complying normalisation rules. Here are some quick links about them:
http://databases.about.com/od/specificproducts/a/normalization.htm
http://moryadesigns.wordpress.com/2009/09/08/rules-of-data-normalization-sql/
1NF Eliminate Repeating Groups – Make a separate table for each set of related attributes, and give each table a primary key.
2NF Eliminate Redundant Data – If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF Eliminate Columns Not Dependent On Key – If attributes do not contribute to a description of the key, remove them to a separate table.
BCNF Boyce-Codd Normal Form – If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF Isolate Independent Multiple Relationships – No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF Isolate Semantically Related Multiple Relationships – There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF Optimal Normal Form – a model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF Domain-Key Normal Form – a model free from all modification anomalies.
Yii uses extensive lazy-loading and the will not load related elements until you ask for them and use them.