I have a little issue with a badly designed table where there is a relationship (many 2 many)!

I did a quick google and microsoft say you have to create a 'junction' table.

So i have to create another table that contains the column as a unique key and then crosslink them so they are one to many via the 'junction' table.

Why if logically the concept of many to many is fine, yet physically you can't directly make that definition?

It also doesn't mention anything about how you populate the 'junction' table?

http://msdn.microsoft.com/en-us/library/aa176955(v=sql.80).aspx

And how do inserts work? If I insert into one of the 'real' tables, does it add a record in the 'junction' table?

Advice is appreciated.

1DMF.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

[link http://dance-music.org]Free Electronic Dance Music Download

Here is an example from my database.

I am storing students in one table, and addresses in another.  My application allows you to have multiple addresses per student.  My application allows you create school bus routes where you could be transporting students to various locations depending on the day of the week, morning vs. afternoon etc.

My Student table has StudentId, Name, etc...
My Address table has AddressId, AddressLine1, City, state, etc...

I have a StudentAddress table that has StudentId, AddressId, Description.

Typical values would be....


StudentAddress
StudentId  AddressId  Description
---------  ---------  -----------
101        202        Home
101        203        Mail
101        204        Grandma
101        205        Babysitter
303        404        Home
505        606        Home and mail


You can set up foreign keys here, and you can even have it cascade delete.  So... if you delete a student, it automatically deletes the rows from StudentAddress.  The logic being, why have student address data for a student that has been deleted.  

I don't cascade delete for the address because I don't want users to delete addresses that may be used for a different student.  For example, if a sibling graduates and you delete her, but her brother is still in school using the same address, you wouldn't want to delete the address because it is still being used.

Quote:

And how do inserts work? If I insert into one of the 'real' tables, does it add a record in the 'junction' table?

You don't normally automatically insert in to the junction table.  

Using my example from above, if a new house is built and a child moves in from outside the district, I collect the data from the user (front end), then I insert a row in to the student table followed by inserting a row in to the address table, and then finally insert a row in to the student address table.

Junction tables are usually such that you don't need to have rows in them.  You can also have 1 row (per main table entry) or multiple rows per main table entry.

Going back to my example above, if the child is homeless, there wouldn't be any rows in the StudentAddress table for them.

Does this help?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Not really because if I created a relationship via a 'junction' table I now have to alter a tonne of code to keep that populated as well.

My scenario is this....

I have a contacts table with CompanyName, there are several contacts at the same company.

I have other tables that store company name, one in particular is an introducer table, and several introducers can introduce to the same company.

So now I have the many to many relationship.

If the main contacts table CompanyName is changed I want all tables regardless of the (1 to x , x to x , x to 1) relationship type, to all get updated with the company name change.

The way the original contact is created is via a bound form in an access front end application.

So they are created in the contacts table, so having a 'junction' table isn't going to help as nothing is populating it.

I would have to rewrite the front end app so it no longer uses a bound field on the contact form, but now some mechanism that popped up a GUI to create companyname that populated both 'contacts' and 'junction' table.

All I would want in the 'junction' table is 'CompanyName' as a single field and the PK.

Is my logic right on this?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

[link http://dance-music.org]Free Electronic Dance Music Download

Having pondered this a little further although I could create a sepatate 'companynames' table, this isn't going to be of help.

Due to the way they like to runs website scrapes and dump 100's of dynamically created records into the contact table as prospects where some don't even have a company name, it starts to get really messy.

I already have code that when companyname gets changed, updates the other tables (and records in same table),So I think I need to leave it as it is and just have a virtual relationship in the schema.

If I was building the system from scratch I'd do many things differently, but a decade of legacy code and DB structure is such that some refactoring just isn't going to work.

Well unless I rewrite a tonne of code in various systems, not practical, really

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

[link http://dance-music.org]Free Electronic Dance Music Download