Junction / Union Tables?

[ssoss]

Hi.

I am creating a database that tracks organizations and their members. After
trying to post several questions in the last couple of weeks, I figured out
the basic structure of my database was lacking.

I have two tables

members
memID, memName, etc.
orgs
orgID, orgName, etc.

The issue is that an organization may have many members, and a single member
may belong to multiple orgs.

Therefore, I cannot use orgID as a FK in the "members" table.

Instead, I thought I would use what I have heard called a "union" or
"junction" table. ie:

x_mems_orgs
xID, memID, orgID

Can someone please give me some advice or samples of how to best implement
this type of functionality in .net?
Specifically, I need an example of filling a DataSet with content of two
tables based on a union/junction table?

Is this necessary or is there an easier way?

Thank you!

Drew
NY

 

[Alex Potter]

Hi there ssoss,

I'm not sure if this helps, but there is a similar relationship in the pub
database
between the titles, autors, and titleauthor tables...

You can create titles, you can create authors and the titleauthor table make
the
link between a given title having multiple authors etc...

It is the titleauthor table that manages the foreign key constraints
ensuring that for a given
row, the author and title already exist in their corresponding tables
(authors, titles)

Hope this helps

 

[dip]

There's a JOIN helper class available on the MS Support
site :

http://support.microsoft.com/default.aspx?scid=kb;en-
us;326080

 

[ssoss]

Alex,

Thanks for the help!

I did use the pubs database titleview code for my select query for the orgs,
with a many to many relationship and two DataTables bound to two datagrids.

This is what I ended up with...

data_table = "main_identity"
field_names = "main_id, dept_id, pos_id, sal_id, main_full_name,
main_email, main_password, main_auto_login"
sql_string = "SELECT " & field_names & _
" FROM " & data_table & _
" WHERE main_email = '" & User.Identity.Name & "' "
data_adapter = New SqlDataAdapter(sql_string, sql_connection)
data_adapter.Fill(data_set, data_table)

data_table = "main_identity,x_main_org,organization_company"
field_names = "organization_company.org_id, org_name, org_type_id"
sql_string = "SELECT " & field_names & _
" FROM " & data_table & _
" WHERE main_email = '" & User.Identity.Name & "'" & _
" AND ( main_identity.main_id = x_main_org.main_id" & _
" AND organization_company.org_id = x_main_org.org_id )"
data_adapter = New SqlDataAdapter(sql_string, sql_connection)
data_adapter.Fill(data_set, "organization_company")

I know my code is kind of convoluted but... I like to be different. I use
code that can be copied and pasted easily into another area, with variables
for those things which are, uh, variable. Also this technique keeps my lines
short, which is great when I want to post to a hard-wrapped format (like
this!)
Anyhow, the code works, so far! Two datagrids with many-to-many
relationship.

Thanks again for the help!

Any suggestions on code style, technique, etc. would be appreciated!


Drew
NY