Join Table basics?

[Birdman895]

I am starting on a Join table in my database. I know I will have; a FK from Table1, a FK from Table2, a PK in the Join Table, and I need a date field at least in the Join Table. I would like to place 3-5 fields additionally in the Join Table.
Should I add these fields to the Join Table or not?
How will this affect performance?
Should all these fields be indexed?

I will be building a schedule on this, scheduling Clients into the different Classes we provide. Eventually it will be a core part of tracking every activity of a Client at our facility. Potentially having thousands of records with multiple people running queries in it at the same time.
Any help and comments would be appreciated.
Alan

 

[jdraw]

You have highlighted a specific issue with a "join table" and raised some related questions.

In plain English can you tell us about Table 1 and table2 -- what are these really? Why do you think a "join table" is needed?

I'm not familiar with the term "join table" - but is sounds similar to a junction table or linking table?

Are you familiar with Normalization?

Here is a tutorial that may put many concepts into context and help answer your questions via an example.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

Good luck with your project.

 

[Birdman895]

Yes, I am talking about a Junction table. I was using FK for foreign key and PK for primary key.
-Table1 has all of the Classes that we have during a week, 50 total. It has the Class Name, Desc., Day, and Time.
-Table2 is the Client table and has all relevant Client info

-Each client can be in 1 -4 classes per week, for 8,12,21, or 26 weeks each.
-Each Class can have up to 15 Clients attending
They have a many-to-many relationship, correct?

I know enough about Normalization to check Enforce Referential Integrity in the relationship definition. I am going now to look at the link that you gave. Thanks for the reply.

 

[jdraw]

Does this data model give any insight into relationships?
http://www.databaseanswers.org/data_models/student_rosters/index.htm

You can not have (in final model) many to many. You resolve many to Many via Junction table that creates two 1:Many relations. And you can have additional fields in the Junction table as required - such fields are dependent on the PK of the Junction Table ( date of this persons class, amount paid for OrderedItem, Quantity of OrderedItems...)

see this youtube video (Many to Many)
https://www.youtube.com/watch?v=7XstSSyG8fw

 

[Birdman895]

I understand, I was trying to justify why I need a Junction Table.
I went to that first link you gave for Rogersaccess... and read the multiple Normalization posts. I'm half way thru Entity-relationship Diagramming. Thanks for the help

 

[Pat Hartman]

Attribute placement is not arbitrary so "Should I add these fields to the Join Table or not" is not really a valid question. The question is "where do these fields belong".

There are many cases where you would add columns to a junction table. A common case would be a ClassRoster. It is a junction table that implements the many-to-many relationship between students and classes. One data field you might include is FinalGrade. OrderDetails is a junction table the joins Orders to Products and that table usually has a number of columns such as quantity, unitPrice, TaxibleFlg, etc. If it takes BOTH FKs to define the attribute, it belongs in the junction table.

 

[Birdman895]

Thanks!
I understand. The other columns would be Attendence, Level of participation, Homework given..
I think that it fits exactly what you were saying, it does take both FK's to define it.