What do I need to know?

[fife]

OK i'll try and explain this as best I can.

 

 

I have members of my site and activities on my site.  Each have thier own ID and are in their own table.

 

member = 000001

activity = 000001

 

Now in a new table called running_activities I have the following fields.

 

ID = 000001

memberID = 000001

activity = 000001, 000002, 000003 etc

 

A member can be part of many activities so I want to store each activity they do in the field activity.

 

Can somebody please explain to me what I need to be learning to do this.  I only know how to store one entry in a field at the moment not lots of separate ones.  Not only that but I would not know how to call just one entry from this field.

[KevinM1]

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[fife]

yes thats exactly what im trying to do.  My database is set up correctly but I think I explained myselt wrong.  Im looking for a tut for entering numerous entries into one field i a database

 

ie

 

in a book store 1 member can have many books so in the database you would have

 

member_id = 001

name          = dave

book_isbn  = 012, 013, 014;

 

From here you could single out book 013  if you needed to.  I need to know the code for entering new book_isbn's without deleteing others.  I have heard something about explode but never used it.

 

I would very much like to learn how to do this so what code do I need to look at?

 

[KevinM1]

The whole point of the link I gave you was to illustrate that you really don't want to have multiple entries per column.  You need multiple database tables.  Activities should have their own table.  And if the relationship between them and members is many-to-many (activities can have multiple members, and members can take part in multiple activities), then you'll need another table (often called a pivot table) to map that relationship.

[fife]

I see.  I really need to think about this then

[KevinM1]

Really take the time to read through the link I gave you.  To use your bookstore example, ISBNs would not be listed as a comma separated string in a member's row.  Instead, it would be part of a book's row.  The link between book and member would come from a foreign key.  The placement of that foreign key would depend on what kind of relationship should exist between the two.

 

To use another example, blog posts and their comments represent a one-to-many relationship.  One blog post can have many comments, but each comment is meant for one particular blog post.  In this case, the primary key of the blog post would be the foreign key for each comment relating to it.  So, you'd have something like:

 

Table Comments

comment_id                                blog_id                             comment_text
1                                         55                                  "Awesome!"
2                                         55                                  "I agree!"
3                                         55                                  "But what about Snoopy?"
4                                         90                                  "Thanks for the link."
5                                         02                                  "I really can't agree with this at all."
6                                         02                                  "Me neither."

 

For many-to-many relationships, like I said before, a pivot table is used.  This is simply a table that contains the primary key of each related item and maps them together.  Think of video games and platforms - each platform (PlayStation, XBox, etc.) has a library of games, and a lot of games are available on multiple platforms (Grand Theft Auto is available on PlayStation, XBox, and PC).  To relate them, you need a purpose-built table:

 

Table Games_Platforms

id                                                game_id                              platform_id
1                                                 04                                   55
2                                                 04                                   77
3                                                 04                                   94
4                                                 10                                   77