What Kind of Relationship?

[eyal8r]

Hey guys-
I have a db where I'm not sure what kind of relationship I need to establish between the tables. I have a db that manages real estate properties and realtor information. Each record can have 3 separate Realtors represented (or, as little as 1 realtor). Each Realtor has their own office, or, might have the same office as the other realtors. So, one record can have anywhere from 1 to 3 Realtors associated with it.

I have a Property Table (holds all the info on the property as well as the Realtor's unique alphnumerica ID Code).

I have a Realtor Table (holds all the contact info for the Realtor, as well as their unique alphanumeric Office Code)

And I have a Realtor Office Table (holds the contact info of the Realtor's Office).

In the Properties table- I have 3 fields for the 3 separate Realtor's ID Codes (like an employee number). Let's call it Agent1Code, Agent2Code, and Agent3Code. There won't always be an Agent2 and Agent 3 in the data- but sometimes there is.

How do I set this up and establish the proper relationships?

 

[dhookom]

I would create a Realtor Property junction table that stores the Realtor primary key and the Property primary key values.

I would never use Realtor1, Realtor2, Realtor3 fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[eyal8r]

ok- so what determines the Realtor's position on the property (ie- 1, 2 or 3?) Right now- it comes in from a flat file, where IF there are multiple realtors on a property- it is designated by their code being in the Realtor1, Realtor2, or Realtor3 fields (otherwise they're null).

So do I place a 'position' field in the junction table? If so, does this ALSO become a primary key with the other 2?

I assume I would then use 3 queries to place the position in the junction table (if <> Null position = 3 or whatever) right?
Thanks for the help!!!

 

[dhookom]

You didn't mention anything about the significance of "position" of realtors in your orginal posting. If you want to store another value, create another field in the junction table.

You can create a unique index on Property and Position as well as Property and Realtor. I always create a Primary Key on an autonumber field.

If you currently have 3 realtor fields, you can create records in the junction table with either 3 append queries or a single append query based on a union query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[eyal8r]

ok- so I can throw a position field into the junction table- and then use append queries to write that realtor's code, propertyID and position into the table? Well- actually, the Realtors code will go into it's own table, and property ID will as well. Position goes into the junction table... does the position need to be a primary key as well?

 

[dhookom]

A table can have one primary key. As I suggested my practice is to always create an autonumber primary key. You can create Unique indexes to force unique values of:
Property - Realtor
Property - Position

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

So if there are multiple Realtor is the first one the primary, the second the secondary and the third the tertiary? In other words, the order the realtor comes in on the flat file determines the priority?

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins