Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-08 : 10:11:18
|
| Joel writes "I am creating a user database that will span multiple child companies of a parent company. I need to correlate each user with the company they have subscribed with. Because it is possible they will subscribe with multiple companies I need to associate them with those companies. So the way I have it set up at this point is:[uList] containing the users data[cList] conatining the companiesIn [uList] I have a field that represents company affiliation and at this point the data stored would look like:(1,2,4) which is a relationship with the ID in the [cList]The whole idea is to keep the company listing dynamic so the parent can add new companies as they see fit. Also the user list is kept in the same place to avoid multiple entries for the same user.My questions is:Is this the best way to go about it? I am interested in maintaining a clean DB structure as well as keeping everything running smooth and quickly.If I needed to run a query for companies, would it be something like(basic):SELECT *FROM uList, cListWHERE cList.ID IN (uList.Company_Affiliate)This is where I am a little lost as far as structure and accessing the data. " |
|
|
Jay99
468 Posts |
Posted - 2002-03-08 : 10:37:22
|
Usually, when you have a many-to-many relationship (such as your user/company situation here) you have a design such as....tblUser tblUserCompany tblCompany-------- --------------- -----------UserID --------->UserID |---> CompanyIDUserName CompanyID ---------| CompanyName.... .... ...... then you wouldselect *from tblUser u inner join tblUserCompany uc on u.UserID = uc.UserID inner join tblCompany c on uc.CompanyID = c.CompanyID Jay |
 |
|
|
|
|
|
User correlated to several affiliations