MySQL duplicate foreign key data or joins?

I'm pondering the best method to handle Users and Accounts for a website.

Every User belongs to a single Account, Accounts can have multiple Users. The tables will be MyISAM so there is no DB-enforced referential integrity. Each User will have permission to view/add/edit their own content and/or the content of all Users for the Account they are under.

CREATE TABLE account (
id INT,
name VARCHAR,
... etc
);

CREATE TABLE user (
id INT,
accountId INT, // references account.id
userName,
etc.
);

Pretty much every other table in the DB will reference the User table. Eg.

CREATE TABLE product (
id INT,
userId, // references user.id
name VARCHAR,
details TEXT
.. more stuff
);

CREATE TABLE event (
id INT,
userId INT,
name VARCHAR,
date DATETIME,
..etc
);

So to get products a User can access assuming the have permission to access just their own it's:

SELECT * FROM product WHERE userId = 17;

To get products a User can access when they have access to the entire account is:

SELECT p.* FROM product p, user u WHERE u.accountId = 3;

Now, the question is: Would it be better to have an accou开发者_JS百科ntId field in product, event etc. as well?

CREATE TABLE product (
id INT,
userId, // references user.id
accountId, // references account.id
name VARCHAR,
details TEXT
.. more stuff
);

This would remove the need for the extra join on just about every query used:

SELECT p.* FROM product p.accountId = 3;

The Users will never move from one Account to another so the accountId will always be correct. Is it worth the additonal data storage requirments and losing a bit of normalisation to remove these joins from the 100s of other queries that the site will use? One other thing to consider is that the user table will not be written to all that often so it's unlikely there will be issues around table locking while doing the joins.

---

Probably not. I think its a waist storing the extra field in every row, and if your tables are indexed properly, Joining them will not be that expensive.

Try adding a index on the user table, to include both the userid and the accountid fields, since your table is MyISAM, that will remove the need to lookup the accountid in the table data.

Also, your query SELECT p.* FROM product p, user u WHERE u.accountId = 3; is performing a OUTER join, which should be changed to

SELECT p.*
FROM product p JOIN user u ON p.userid = u.id
WHERE u.accountId = 3;