Join performace

Question

My situation is:

Table member
id
firstname
lastname
company
address data ( 5 fields )
contact data ( 2 fields )
etc

Table member_profile
member_id
html ( something like <h2>firstname lastname</h2><h3>Company</h3><span>date_registration</span> )
date_activity
chat_status

Table news
id
member_id (fk to member_id in member_profile)
title
...

The idea is that the full profile of the member, when viewed is fetched from the member database, in for instance a news overview, the smaller table which holds the basis display info for a member is joined.

However, i have found the need for more often use for the member info that is not stored in the member_profile table, e.g. firstname, lastname and gender, are nescesary when someone has posted a news item (firstname has posted news titled title.

What would be better to do? Move the fields from the member_profile table to the member table, or move the member fields to the member_profile table and perhaps remove them from the member table? Keep in mind that the member_profile table is joined a lot, and also updated on each login, status update etc.

Answer 1

You have two tables named member so i have the feeling your question isn't formed correctly.

What is the relationship between these tables? It looks like you have 3 tables, all one-to-one. So all you need to do is change (fk to member_id in member_profile) to (fk to id in member).

Now you can join in data from either of the 2 extra tables as you wish, without always having to go through member_profile.

[Edit] Also I assume that member_profile.member_id is a fk to member.id. If not, I believe it should :)

Answer 2

Combine them into one table so you're normalizing the name data then create 2 views which replicate the original two tables would be the easy option

Answer 3

Separating the tables between mostly-static fields and frequently-updated fields will improve write performance. So I would stay with what you're doing. If you cache the information from both tables together in a member object, read performance (and thus joining) is less of an issue.