Many to many relationship (noob)

[standstill]

Ok, I guess this is a really basic question so sorry, but I'm not able to find it exactly.

I just want to have 2 tables.

STUDENTS with the typical personal data (name, address, phone, etc)

COURSES with the name of the course, the hours, teacher, etc.

A Student should be able to attend to multiple Courses and also, a Course can have multiple Students. The idea is to show a portal on a presentation for each table.

I know how to make it "one to many" but not both sides.

Thank you very much!

[comment]

You need a third table, say Enrollments, where each record is a single instance of a student joining a class. See an example here:

http://www.fmforums.com/forum/showpost.php?post/246136/

[standstill]

Thanks, this is quite what i'm looking for.

I've taken a look and tried to replicate the scheme but although I create the portals, nothing appears so either is something wrong with the way I did the relationship or the way I called the fields for the portal... :S

Is it necessary that the third (bridge) table has a unique ID field of its own? I haven't done that since in my case that table is more "accessory" than in the example and its ID isn't related with the others...

Done, it works... it was my fault.

Thanks a lot!

[comment]

Is it necessary that the third (bridge) table has a unique ID field of its own?

It's not necessary now - but it's good practice to have a serial ID in every table, because you might need it later.

[LaRetta]

Hi standstill,

A unique ID in every table isn't only used if you want to create a relationship. Unique IDs provide ability to update your data in case of crash or other problems. For instance, if a User accidentally changes many fields in a record and Management requests that the values be put back to what they were from the prior backup, it is this unique ID which must be used to match during the import. If you wait until you NEED an ID, it is most often too late.

This is same reason every table should have a creation and modification timestamp ... there will be times that you need to retrieve data according to when it was last modified, or find all records created after a certain point in time.

These three fields should be in every table and it has become one of my rules because of NOT having them and needing them too many times over the years during critical situations. :laugh2:

[standstill]

I'll definitley add them then. Thanks for the advice, I appreciate it. ;)

[comment]

Unique IDs provide ability to update your data in case of crash or other problems.

:goodpost: