0

So I've tried searching and have yet to find out how to grasp this entirely.

I'm reorganising my database because I was storing user id's as comma separated values in a column withing that row to control permissions. To me, this seems like a better and faster(hardware) way, but I'm moving towards this "proper" way now.

I understand that you need 3 tables. This is what I have.

Table 1. members -> ID | user_name
Table 2. teams -> ID | team_name
Table 3. team_members -> ID | team_fk | member_fk

I understand how to store data in another column and use sql data to display it. What I'm confused about is why I have to link(relation) the columns to the ID's of the other tables. I could get the data without using the relation. I'm confused by what it even does.

Furthermore, I would like to have multiple values that determine permissions for each team. Would I do:

Table 3. team_members -> ID | team_fk | member_fk | leader_fk | captain_fk

^setting 0 or 1(true or false) for the leader and captain.

Or would I create a table(like team_leaders, team_captains) for each permission?

Thanks for the help!

Ryan

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • "I could get the data without using the relation". How would you get the members of a team without using the relation? – Barmar Oct 27 '16 at 22:02
  • can there be more than one captain or leader of a team? If not, they should be columns in the `teams` table, not attributes of the members. – Barmar Oct 27 '16 at 22:06
  • Barmar, I would first get the ID of the team, then use that in a mysql select and a foreach. Yes there can be multiple, which I had separated by commas. –  Oct 27 '16 at 22:38
  • To further explain, lets say I load the team page and get the id for that team. I then do a query with a while loop that inserts all the member id's into an array based off that team ID. I could have array data that has their member id and what they belong to in that. –  Oct 27 '16 at 22:41
  • But the whole point of this was to get rid of the comma-separated list. – Barmar Oct 27 '16 at 22:41
  • The comma separated list is within the columns currently. If I do the above structure, there wouldn't be. Each member has a row with values. –  Oct 27 '16 at 22:42
  • In the above structure, where would you get the team members from without the `team_members` table? There's nothing else that relates them. – Barmar Oct 27 '16 at 22:44
  • Sorry if this is confusing. Basically it would be a multidimensional array. My website loads a page which gets the team ID via the url. So once I do that, I could query that ID to find matches in the team_members. Then I would just do an each function. –  Oct 27 '16 at 22:47
  • I thought your question was why you need the `team_members` table. So I wanted to know what your query would be without that table. – Barmar Oct 27 '16 at 22:48

2 Answers2

0

It seems that "leader", "captain and "regular member" are roles in your team. So you can create table team_roles, or just assign roles as strings to your relation table, i.e.

team_members -> ID | team_fk | member_fk | role

The key thing about this is to keep your database [normalised]https://en.wikipedia.org/wiki/Database_normalization. It is really easier to work with normalised database in most cases.

lamik
  • 89
  • 1
  • 7
0

What I'm confused about is why I have to link(relation) the columns to the ID's of the other tables. I could get the data without using the relation.

You don't have to declare columns as foreign keys. It's just a good idea. It serves the following purposes:

  1. It tells readers of the schema how the tables are related to each other. If you name the columns well, this is redundant -- team_fk is pretty obviously a reference to the teams table.
  2. It enables automatic integrity checks by the database. If you try to create a team_members row that contains a team_fk or member_fk that isn't in the corresponding table, it will report an error. Note that in MySQL, this checking is only done by the InnoDB engine, not MyISAM.
  3. Indexes are automatically created for the foreign key columns, which helps to optimize queries between the tables.

Table 3. team_members -> ID | team_fk | member_fk | leader_fk | captain_fk

If leader and captain are just true/false values, they aren't foreign keys. A foreign key column contains a reference to a key in another table. So I would call these is_leader and is_captain.

But you should only put these values in the team_members table if a team can have multiple captains and leaders. If there's just one of each, they should be in the teams table:

teams -> ID | team_name | leader_fk | captain_fk

where leader_fk and captain_fk are IDs from the members table. This will ensure that you can't inadvertently assign is_captain = 1 to multiple members from the same team.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • There are tons of ways to display the data, what I'm confused about is foreign keys. What the heck is it doing? Why do we do it? I've googled a lot of things and can't find the answer. –  Oct 27 '16 at 22:49
  • If you don't put the foreign keys into the junction table, then how would it tell you which members are in which teams? What else would you put in the junction to relate them? – Barmar Oct 27 '16 at 22:51
  • Basically I've linked team_FK to the teams table ID, and member_FK to the users table ID. What does this even do? lol –  Oct 27 '16 at 22:51
  • So if you have a row with `team_fk = 1, member_fk = 3`, it means that the player with `ID = 3` is a member of the team with `ID = 1`. – Barmar Oct 27 '16 at 22:52
  • Which I get. But I don't need to set the relation in order to retrieve that data and display it. That's what I'm trying to say, why does it need to be done? I really appreciate your time and help with this. –  Oct 27 '16 at 22:55
  • Are you just asking why you need to declare the columns with the `FOREIGN KEY` constraint? That just enables automatic integrity checking. it won't let you create a row that refers to a team or member that doesn't exist. – Barmar Oct 27 '16 at 23:01
  • Okay so I see what you changed in your post. It makes sense, but it's exactly what I'm confused. Why do I need to set the relation? I could use that query and inside that query I can then do another query to get the info of the member... –  Oct 27 '16 at 23:02
  • In phpMyAdmin I went into relation view and set the relations to the ID's. I just don't understand why I do this. –  Oct 27 '16 at 23:03
  • I've updated my answer to explain why the FK relationship is used. – Barmar Oct 27 '16 at 23:05
  • Thank you very much, I appreciate it. I thought I was missing something. No where did I read on any tutorial or example did it say it was just 'good practice'. Thanks a million. –  Oct 27 '16 at 23:07