solution1368
asked on
sql query
Agency Table:
AgencyID AgencyName
100 John
101 Dave
Agent Table
AgentID AgencyID AgentName
1 100 John's Friend
2 100 Jonh's another Friend
3 101 Dave's friend
Agency/Agent has 1 to many relationship in the database table.
If I want to make the Many to Many relationship, what you suggest me to do?
AgencyID AgencyName
100 John
101 Dave
Agent Table
AgentID AgencyID AgentName
1 100 John's Friend
2 100 Jonh's another Friend
3 101 Dave's friend
Agency/Agent has 1 to many relationship in the database table.
If I want to make the Many to Many relationship, what you suggest me to do?
ASKER
can't remove any columns because we already have the data there and also with alot of stored procedure being used.
It is just we cannot modify too many things.
It is just we cannot modify too many things.
My guess is that you'll have a bigger mess to deal with if you try to solve the issue while keeping the column there. To start the change, you would insert a row into the Agent_Agency for each Agent, which would store your existing relationships.
Yes, any stored procedures etc would need updating, but in my experience you'll have a bigger headache down the track if you don't do it and implement a compromised schema.
Other experts may like to discuss this further though?
Yes, any stored procedures etc would need updating, but in my experience you'll have a bigger headache down the track if you don't do it and implement a compromised schema.
Other experts may like to discuss this further though?
I agree with TerryAtOpus, as the alternative would be to alter the Agent table to be able to hold multiple agents. This will also cause the stored procedures to have to change as the AgencyID won't be unique anymore...
Just create the relation table tblAgencyAgent like proposed and I would add a StartDate and EndDate, thus allowing to get historical data.
Just create the relation table tblAgencyAgent like proposed and I would add a StartDate and EndDate, thus allowing to get historical data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Good coding sameer2010, but what to do when the agent's name needs to be changed in an agents edit form...
This classical redundancy problem is introduced by not normalizing the n:m relation with a relation table.
This classical redundancy problem is introduced by not normalizing the n:m relation with a relation table.
@solution1368, can you please confirm that the accepted solution was the one and only solution posted that solved your problem? If not, then we should use the Request Attention link (just below your question) to get it reopened so the correct solution can be selected.
ASKER
please close this question
ASKER
I've requested that this question be deleted for the following reason:
Please close this question. The solution provided by the experts do not qualify by the admin in experts-exchange.
Thanks
Please close this question. The solution provided by the experts do not qualify by the admin in experts-exchange.
Thanks
ASKER
Please close this question. The solution provided by the experts do not qualify by the admin in experts-exchange.
Thanks
Thanks
It's not that the admin think a solution does not qualify for acceptance; instead the issue is that the author has accepted solutions that aren't actually a solution to the problem, thus their action has been reversed.
As I see it, the author has been given a third chance to accept a solution, but has misunderstood the reason why this sequence of events has occurred and is now wanting to sweep the issue under the carpet by deleting the question!
If the author is unable to select a suitable solution, then a forced accept should be done.
As I see it, the author has been given a third chance to accept a solution, but has misunderstood the reason why this sequence of events has occurred and is now wanting to sweep the issue under the carpet by deleting the question!
If the author is unable to select a suitable solution, then a forced accept should be done.
Reposted as an objection!
It's not that the admin think a solution does not qualify for acceptance; instead the issue is that the author has accepted solutions that aren't actually a solution to the problem, thus their action has been reversed.
As I see it, the author has been given a third chance to accept a solution, but has misunderstood the reason why this sequence of events has occurred and is now wanting to sweep the issue under the carpet by deleting the question!
If the author is unable to select a suitable solution, then a forced accept should be done.
It's not that the admin think a solution does not qualify for acceptance; instead the issue is that the author has accepted solutions that aren't actually a solution to the problem, thus their action has been reversed.
As I see it, the author has been given a third chance to accept a solution, but has misunderstood the reason why this sequence of events has occurred and is now wanting to sweep the issue under the carpet by deleting the question!
If the author is unable to select a suitable solution, then a forced accept should be done.
ASKER
This codes work to me and logic works as well. Thanks.
AgentAgencyID (just a unique id for the relationship)
AgentID
AgencyID