MYSQL tables question

[BeauJangles]

So, I've been using patUser (if anybody is familiar with it) to make a secure login system for my website. In customizing it to suit my needs, I ran across an interesting problem. Basically, each of my users can belong to up to five teams and I want a way to easily store this information.

What I'm thinking about doing now is creating a teams table which will have a team ID (tid) and name field.

teams
tid name
1 1999-2000 team a
2 1999-2000 team b
3 2000-2001 team a
etc....


then i was going to create another table called userteams or something which would look like this:

userteams
id tid uid (for userid)
1 12 1
2 13 1
3 14 1
4 15 1
that way when i wanted to query all the members of team 12 i could just return all instances of the tid in the userteams field and look up the corresponding uid.

Does this make sense or will the userteams get too long too quickly?

 

[statik213]

looks fine to me.
I doubt if the userteams would grow too large too quick, at most you will have 5x as many entries in the userteams tbl as in the users tbl 'cos a user can belong upto 5 teams.

 

[lansalot]

Don't worry about table length. That should be fine.

 

[BeauJangles]

thanks guys!

 

[oog]

though i usually like adding an id column to all my tables, i don't think it's necessary in userteams. i think you can just have the user id and the team id and that's it.

 

[notfred]

There's no point to having in ID field in that userteams table.

And when you want to find all the members of team 12, you do this:
select users.name from users, userteams where userteams.uid = users.uid and userteams.tid = 12;

Which is a lot faster than what it sounds like you want to do.

 

[toekramp]

Originally posted by: notfred
There's no point to having in ID field in that userteams table.

And when you want to find all the members of team 12, you do this:
select users.name from users, userteams where userteams.uid = users.uid and userteams.tid = 12;

Which is a lot faster than what it sounds like you want to do.

pfffh, always always always have a unique identifer...

unless you are saying the unique identifer is the combination of the 2 IDs

 

[notfred]

Originally posted by: toekramp

Originally posted by: notfred
There's no point to having in ID field in that userteams table.

And when you want to find all the members of team 12, you do this:
select users.name from users, userteams where userteams.uid = users.uid and userteams.tid = 12;

Which is a lot faster than what it sounds like you want to do.

pfffh, always always always have a unique identifer...

unless you are saying the unique identifer is the combination of the 2 IDs

Of course it is. The primary key for that table should include both the uid and teamid fields, and nothing else. A third field adds nothing useful to the table.

Seriously, if you had to question that, you might want to go back and take databases 101 again.

 

[toekramp]

Originally posted by: notfred

Originally posted by: toekramp

Originally posted by: notfred
There's no point to having in ID field in that userteams table.

And when you want to find all the members of team 12, you do this:
select users.name from users, userteams where userteams.uid = users.uid and userteams.tid = 12;

Which is a lot faster than what it sounds like you want to do.

pfffh, always always always have a unique identifer...

unless you are saying the unique identifer is the combination of the 2 IDs

Of course it is. The primary key for that table should include both the uid and teamid fields, and nothing else. A third field adds nothing useful to the table.

Seriously, if you had to question that, you might want to go back and take databases 101 again.

well my issue is with indexing so please dont' try and be a jackass

 

[oog]

Originally posted by: toekramp

Originally posted by: notfred

Originally posted by: toekramp

Originally posted by: notfred
There's no point to having in ID field in that userteams table.

And when you want to find all the members of team 12, you do this:
select users.name from users, userteams where userteams.uid = users.uid and userteams.tid = 12;

Which is a lot faster than what it sounds like you want to do.

pfffh, always always always have a unique identifer...

unless you are saying the unique identifer is the combination of the 2 IDs

Of course it is. The primary key for that table should include both the uid and teamid fields, and nothing else. A third field adds nothing useful to the table.

Seriously, if you had to question that, you might want to go back and take databases 101 again.

well my issue is with indexing so please dont' try and be a jackass

the queries are probably not going to use a separate id from the uid and tid and therefore even from an index perspective, the extra id added to the userteams table is useless. the proper indexes would definitely be on the columns that you use for joining or for reducing your resultset. in both cases, you're talking about uid and tid and not the extra id.