List of values -- db design

[error792]

I'm making an app that involves people taking classes. I have a table for users and a table for classes; now I need to store the list of classes each user is taking, in such a way that I'll be able to easily (efficiently) select which users have a given class.

The problem is I'm not sure how to store lists. From what I've heard on the web storing a comma-separated list of class ID's would mean I'd have to parse that with PHP, which is obviously inefficient. The only other solution I can see is to have another table with a row for each user/class association, but this seems inefficient to me. What's the best way to implement this?

Thanks in advance.

[SKDevelopment]

Each use can take several classes. Each class has several users. This is classical many-to-many relationship.

You need 3 tables:

users
-----
userid
userdata

classes
-------
classid
classdata

userstoclasses
--------------
userid
classid

[Old Pedant]

The only other solution I can see is to have another table with a row for each user/class association, but this seems inefficient to me.

It's not. It's the only *EFFICIENT* way, with relational DBs.

If the many-to-many table (as illustrated by SKDev) is critical to performance, just make sure to index one or both of its fields. (If your usage pattern is such that you almost never "look up" by one of the id's, then you may not need to index it. But do index the one--or both--that is used much of the time.)

[SKDevelopment]

2 Old Pedant: A very interesting post! Thanks for touching efficiency. Sometimes indexes are not really efficient of course. E.g. if the tables are small (under 300 records or so), then indexes could be really not much efficient. Indexes are based on binary trees, which normally are supposed to have something around 300 records in a leaf or so. So on small tables indexes probably should not give increase to performance. Still once I had a situation when adding index to a table with 150 records gave considerable increase to performance to a query with multiple joins... I always check execution plans with EXPLAIN to see how the query is going to be executed.

Still in this case I would set userid as Primary Key for users, classid as PK for classes and (userid,classid) as composite PK for userstoclasses. Even if for small tables it would not increase performance, I think it would not decrease it greatly ...

2 error792: we have discussed a very similar many-to-many relationship in this thread (http://www.codingforums.com/showthread.php?t=175950) a few days ago. I mentioned it because possibly you could find that discussion interesting...

[Old Pedant]

Not a lot of experience with MySQL and small tables. Had a many-to-many table that had about 200,000 records. *All* usage of that table turned out to be via just one of the "sides". Indexing just that id made the lookups lightning fast and of course not indexing the other id made the inserts faster than if we'd indexed both. So, yep, definitely your usage pattern matters.

And I'm sure you are right about small tables. Probably depends a lot on the DB architecture. With a DB I once worked on the internals of, if you specify a primary key, and if the entire table fits in a memory block (64KB on Windows architectures), then there isn't any separate index. The records themselves are kept in PK order. So then even small tables benefit from using a PK, albeit of course not nearly as much as large tables. (I *think* that SQL Server does this same thing, but that's only from reading the docs, so not sure.)