CodingForums.com - View Single Post

Old Pedant · 11-26-2012, 08:20 PM

In other words, as FouLu says, but maybe not explicitly enough, NEITHER of your A or B choices should even be considered. They are both bad ideas.

Many-to-many tables, when properly indexed and properly used, are not only the only viable solution in a relational database environment, they are also the most efficient.

Finding out if Joe has already paid for Feature17 is a trivial and very fast query:

Code:

SELECT COUNT(*) FROM
    users AS U, features AS F, userFeatures AS UF
WHERE U.name = 'Joe'
AND U.userid = UF.userid
AND UF.featureid = F.featureid
AND F.featurename = 'Feature17'

If you already know the userid (you probably do, because Joe logged on 3 minutes ago and you stored his userid in a SESSION value) then you don't need the Users table in the query:

Code:

SELECT COUNT(*) FROM
    features AS F, userFeatures AS UF
WHERE UF.featureid = $userid
AND F.featurename = 'Feature17'

11-26-2012, 08:20 PM	PM User \| #3
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,172 Thanks: 59 Thanked 3,994 Times in 3,963 Posts	In other words, as FouLu says, but maybe not explicitly enough, NEITHER of your A or B choices should even be considered. They are both bad ideas. Many-to-many tables, when properly indexed and properly used, are not only the only viable solution in a relational database environment, they are also the most efficient. Finding out if Joe has already paid for Feature17 is a trivial and very fast query: Code: SELECT COUNT() FROM users AS U, features AS F, userFeatures AS UF WHERE U.name = 'Joe' AND U.userid = UF.userid AND UF.featureid = F.featureid AND F.featurename = 'Feature17' If you already know the userid (you probably do, because Joe logged on 3 minutes ago and you stored his userid in a SESSION value) then you don't need the Users table in the query: Code: SELECT COUNT() FROM features AS F, userFeatures AS UF WHERE UF.featureid = $userid AND F.featurename = 'Feature17' __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.