Creating a form, data entry error and Relationship

[shani908]

Hello everyone.



I am trying to create a form for data entry, I have three tables as following.

1) Agents
2) Company
3) Propertytbl

I have the following Properblems. :

1) All relationship have Data Integrity and Cascade update enabled. I am getting error as shown in the screenshot. I have enable "Data Entry" property to "yes".

2) I think my database design is wrong. My analogy is following :
A) One company can have many Agents.
B) Each Agents can have many listing/Properties.


Please advise.

[Edgar]

With those relationships, each agent can be related to only one property. If you want your properties to be related to only one agent at any given time and also be able to assign multiple properties to any agent, then the design should be as follows:
Company: CompanyID, CompanyName
Agents: AgentID, AgentName, CompanyID
PropertyTbl: PropertyID, PropertyDetails, AgentID

You should also reconsider your naming convention for tables. The error message probably means there are rows in Agents table with CompanyID values that do not appear in Company table.

[xps35]

I agree with Edgar. The question is whether there might be a many-to-many relationship between agents and properties. Then you need an extra junction table.

The error is probably because companyID and propertyID have a default value of 0. That value does not appear in the tables they refer to.

[shani908]

Thanks .. Noted. its working now. Its started working once I added AgentID to PropertyTbl.

With those relationships, each agent can be related to only one property. If you want your properties to be related to only one agent at any given time and also be able to assign multiple properties to any agent, then the design should be as follows:
Company: CompanyID, CompanyName
Agents: AgentID, AgentName, CompanyID
PropertyTbl: PropertyID, PropertyDetails, AgentID

You should also reconsider your naming convention for tables. The error message probably means there are rows in Agents table with CompanyID values that do not appear in Company table.

[shani908]

Thanks . Noted. Can you please give some more details on that. I am a beginner.

Thanks

I agree with Edgar. The question is whether there might be a many-to-many relationship between agents and properties. Then you need an extra junction table.

The error is probably because companyID and propertyID have a default value of 0. That value does not appear in the tables they refer to.

[xps35]

In theory, it could be possible for an agent to belong to multiple properties and a property to multiple agents. Then you need the extra table containing at least the agent and property IDs.

[CarlettoFed]

Attach an example file and you will see that it will be easy to solve the problem because without being able to see what you have created it is more difficult.

[Edgar]

I agree with Edgar. The question is whether there might be a many-to-many relationship between agents and properties. Then you need an extra junction table.

Indeed. If the business rule is "my properties can be related to one or multiple agents" then the junction table will solve it. OP did not specify it, so the simplest solution is a one-to-many relationship of one-agent-many-properties. A many agents and many properties relationship would look as follows:

Company: CompanyID, CompanyName
Agents: AgentID, AgentName, CompanyID
PropertyTbl: PropertyID, PropertyDetails
PropertiesByAgent: Id, PropertyID, AgentID

Which, assuming agents sell the properties, it would allow to add to PropertiesByAgent extra information about this agent-property relationship, such as a date to indicate when the assignment of the property to that seller was made, a field to indicate the commission, another field to indicate status, as well as notes.

Not knowing further about the business, the OP should clarify.

[xps35]

Not knowing further about the business, the OP should clarify.

Once more.............