MySQL Tables

[ACJavascript]

Hello All,

Okay I have a question.

Is there a limit for the amount of Tables a mysql database can have?
---------
My other question

My client wants GROUPS for his members, what I am thinking is creating a table for each group - since each group has a leader ID number and member list.

Would this be a good way to do it?


Thanks for any help anyone can give.

[Kiwi]

Originally posted by ACJavascript
Hello All,

Okay I have a question.

Is there a limit for the amount of Tables a mysql database can have?
---------

I think there is. But I suspect that you'd run into other problems -- such as serever limitations -- before you started straining mySql too much.


My client wants GROUPS for his members, what I am thinking is creating a table for each group - since each group has a leader ID number and member list.

Would this be a good way to do it?Absolutely not.

I assume that you have a table for the people (I will imaginatively assume it's called people). Your best alternative is to create a table called groups -- where you include the relevant information for the group (from what you've said, this would probably consist of group_id as the primary key; leader_id as a foreign key, from the person table; and anything else specific to the group, such as a description of the group, date_formed and so on. You would have a second table called something like member_lists, which would contain member_list_id as the primary key and hardly used; group_id and person_id as foreign keys from the relevant tables; any other information regarding the membership -- such as date_joined, membership_status and so on.

When you create a new group, you would firstly create the group (including assigning the leader), the, once the group is created, you would add members. Personally, I would store things like 'membership_status' and 'group_type' in tables as control variables; but this might be over-kill, depending on the specific application.

[ACJavascript]

Thanks alot kiwi,

I will give ALOT of thought to what you said THANKS! :D

[ACJavascript]

Hello again,

I was hoping kiwi mabye you could help me with the scheme, or sombody else.
Heres what I was thinking please help hehehe.
I just learned mySQL and pretty much working it blindly :p

The Image is the scheme.

[Kiwi]

I'm not sure what you're describing here, exactly. Since team1 and team2 are structurally identical, they should be from one table (which will make adding more teams a matter of data entry, not structural change). If you are going to create a table for each record, you are pretty much wasting the point of a database. That said, I am still pretty unclear on your over-all structure -- and this has made it more confusing, largely because you added teams which weren't in your original specification, and it's not clear how they fit in.

From the structure you've presented, a group consists of two teams and a leader. A team consists of members, and a leader. A member consists of a name. However, you can only have one member in each team (otherwise the team_id will try to be duplicated), which makes the team idea redundant. Furthermore, because the way your structure works, each group can only consist of one member from each team -- also making it largely redundant.

Let's try something like this. All people are entered into one table called people:

CREATE TABLE people
person_id AUTO_INCREMENT UNSIGNED NOT NULL PRIMARY KEY
name VARCHAR(40) NOT NULL
registered_on DATETIME NOT NULL

Then you need a table for each the teams. The team leader is identified, but as a foreign key to people, rather than as a text field. You can also specify the name of the team (eg team1 and team2), and nothing else. Using this approach, you can have as many teams as you like: adding another team only requires one record in this table.

CREATE TABLE teams
team_id AUTO_INCREMENT UNSIGNED NOT NULL PRIMARY KEY
team_name VARCHAR (40) NOT NULL
team_leader MEDIUM_INT (5) NOT NULL FOREIGN KEY (people.member_id)
created_on DATETIME NOT NULL

Then you need to assign people to teams. You do this with a third table, which combines the keys from the two tables you have already created. This structure will allow one person to belong to more than one team; it will allow teams to have as many members as you need.

CREATE TABLE team_members
team_member_id AUTO_INCREMENT UNSIGNED NOT NULL PRIMARY KEY
team_id MEDIUM_INT (5) NOT NULL FOREIGN KEY (teams.team_id)
member_id MEDIUM_INT (5) NOT NULL FOREIGN KEY (person.person_id)
registered_on DATETIME NOT NULL

Finally, you have a group table, which combines the teams into a group (the point of this is beyond me, but I can describe how to do it). Once again, don't enter anything that's already in another table.

CREATE TABLE groups
group_id AUTO_INCREMENT UNSIGNED NOT NULL PRIMARY KEY
team1_id MEDIUM_INT(5) NOT NULL FOREIGN KEY (teams.team_id)
team2_id MEDIUM_INT(5) NOT NULL FOREIGN KEY (teams.team_id)
leader MEDIUM_INT (5) NOT NULL FOREIGN KEY (members.member_id)
registered_on DATETIME NOT NULL


to combine the information from these various tables, you need to run joining queries. For example, to get a list of the member names from one team, you would run something like:
SELECT people.name
FROM people team_members
WHERE
team_members.member_id = people.person_id AND
team.members.team_id = "00001"


I can't guarantee the syntax is correct (I very rarely write sql), but the basic structure is along the right lines. The point is to make sure that you only enter a piece of information (such as a person's name) once and that you combine tables which are structurally and functionally identical.

[ACJavascript]

Hey Kiwi,

I apologize the graphic I gave must have relaly thrown you off. Now that I look at it all I can do is this : :eek: : hehe.

You have really helped me out on this THANK YOU SO MUCH!

I think I get the structure Idea now.

I TRULY DO APPRECIATE IT KIWI :D