CODEWITHSUNDEEP
sqlmysqlnormalization
Alex Rashkov
Alex Rashkov

Reputation: 10015

MySQL: many joins or extra foreign key?

I have following table structure:

CREATE TABLE pilot_groups (
id INT PK,
name VARCHAR(50),
....
);

CREATE TABLE pilot_group_leaders (
id INT PK,
pilot_group_id INT FK,
name VARCHAR(50),
address TEXT,
....
);

CREATE TABLE members (
id INT PK,
pilot_group_leader_id INT FK,
country_id INT FK,
type VARCHAR(50),
name VARCHAR(50),
....
);

CREATE TABLE farms (
id INT PK,
member_id INT FK,
....
);

So I have to produce a report and show all farms grouped by pilot_group. To do it using current structure I'll have to do 3 joins which in my opinion is not very optimized and efficient. I was thinking of placing extra foreign key in the farms table but thus I'll have repetition. The purpose of the website/application would be mainly for producing reports and entering data. Which approach might be better?

Upvotes: 1

Views: 120

Answers (2)

Marco Mariani
Marco Mariani

Reputation: 13766

Dropping the 'id' keys entirely and using natural primary keys might help a lot. But then, saying this will cost me karma :-)

Upvotes: 4

Adriaan Stander
Adriaan Stander

Reputation: 166406

Stick with your current version, dont duplicate the foreign keys.

You should be able to get away with some indexes on the tables.

Dont fix it if it aint broke. Do you actually have performance issues, or are you anticipating them?

Upvotes: 3

Related Questions