What Type 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?

 

[pbaldy]

Any time you see fields with numbers in them, you're probably looking at a non-normalized table. I'd probably have a many-to-many junction table. 2 fields, PropertyID and AgentID. A property with only 1 agent would have 1 record; a property with 3 would have 3 records.

 

[eyal8r]

Right- but understand that each Realtor holds a different position on the transaction- so it's like 3 employees w/ different job functions or descriptions. So I don't know if that's non-normalized or not...

Either way- about this Junction table- how do I set it up, exactly? What field do I need in the Property Table? What type of relationships between them?
Thanks for all the help!

 

[pbaldy]

It doesn't appear that your design accounted for that, but in any case I'd probably add a field to the junction table for it. You wouldn't have any field in the property table. This table would have a field for the key field from the property table (with a one-to-many relationship between the 2) and a field for the key field from the realtor table (again with a one-to-many relationship). Plus this new field for position, which could come from a "Positions" table (you guessed it, one-to-many).

 

[eyal8r]

ok- I setup the Junction Table- do I need a Primary Key for that table, or just the 2 foreign keys?

 

[pbaldy]

That's one of those style things. Some people advocate an autonumber key field for every table. Personally, I'd just make those 2 fields a compound key, thus preventing duplication of any combination (assuming the same Realtor can't have 2 positions or whatever).

 

[eyal8r]

ok- could I...
Have 3 fields in the Property Table (Agent1, Agent2, and Agent3) that all relate to the Junction Table? 1 Agent will never fill the other 2 spots at the same time- but could fill a different position in another record.

I'm just thinking- when I pull a query on the Property Table- how do I see which agent is in which position without including a spot for each of them in the property table?

 

[eyal8r]

ok- how do I make a compound Key?

 

[eyal8r]

ok got it. I shift-Click on both rows in the design table and set the Key on both at the same time. COOL!
Thanks for the helP!

 

[pbaldy]

I would not have any agent fields in the property table. That's what the junction table is for. You pull them with a query by joining the junction table in.

 

[eyal8r]

Well shoot- this doesn't solve the problem tho.

So I have 3 fields in the property Table- Agent1, Agent2, and Agent3. Each a different position in the record. I have the Junction table setup- but- how do I associate each agent's position to the Junction Table?

 

[eyal8r]

ok... I can see where you're going with that- but...

Let's say I query to find out which Agents are associated with which property. How do I tell which agent holds which position on the property (Lead Agent, Co-Lead Agent, and Buyer's Agent)? I assume if I just query the PropertyID and the AgentID I'll get a list of 3 back. But which agent holds which position?

 

[pbaldy]

As I said, I would add a third field to the junction table to hold their position. Include that field in your query and you have the position.

 

[eyal8r]

huh. ok- so then the coinciding position field would go in the Property Table? So I'd have 1 field for AgentCode, and AgentPosition in the property table? I'd also have a AgentPosition field in the Junction Table? Is that right? Then I'd establish another relationship from the Property.AgentPosition to the JunctionTable.AgentPosition and then from the JunctionTable.AgentPosition to the AgentTable.AgentPosition?

I guess I'm having trouble seeing how I would query the all the Agents and their positions with each property. Anymore hints you can give? I'm just not getting it.

 

[pbaldy]

No agent or position related fields in the property table. Can you post a sample db?

 

[eyal8r]

OR would I have a separate table for the 3 positions- and make THAT a junction table between the Agent Table and the Property Table, with both FKs set as a PK in that Junction Table?

 

[eyal8r]

Here's a pic of what my current Relationship table looks like...

NOTE- the MLS is the PropertyID field- and that is linked tot he tbListings table if you scroll up in the table you can see it (just not shown here). The Agent1Code, Agent2Code and BuyerAgentCode are the 3 different agents/positions...

 

[eyal8r]

Here's what's not making sense to me. The import file that I get the data from has all this info in 1 table. I then will be inserting the info into each appropriate table. So- the import file has a field for the Agent1Code, Agent2Code and BuyerAgentCode. When I update query that info into the tables- how do I tell it that the Agent2Code goes to position #2, and BuyersAgentCode goes to Junctiontable.Position #3?

I guess I just see it as easier to have all 3 in their own fields in the property table, as the are when they come in. But I can see how that's not normalized- I guess it's just not making sense now. Anyway I can get you to explain a little more for me?
I REALLY appreciate it!

 

[pbaldy]

You're welcome to leave them like they are, but I suspect you'll run into other problems down the line due to the denormalized design. I didn't realize you were dealing with a flat import file, but I don't think it changes what I would do. I assume you've got an import process to put other data into other tables. The way I would handle this bit is with 3 appends. First appends Agent1 if it isn't Null, with the Property ID and appropriate position. Same for 2 and 3. Thus you'd get 1, 2 or 3 records in the junction table, depending on which of the 3 fields was populated.

 

[eyal8r]

OKAY! So- what I am reading is this-
I take out Agent1Code, Agent2Code and Agent3Code from the Property Table. I put in a 'PositionCode' into the Junction table, as a FK to the 'AgentPosition Table' with a 1 to Many relationship between it and the Junction table. I then run 3 separate queries just for putting the Agent/Position/Property Combos in (1 Query for each position). Is this right?

Questions then-
1) Do I need to include the Position FK as part of the Combination Key in the Junction Table?
2) When I run the queries for the Agent's Position- how do I tell the table that it's position 3 (or whatever)? Do I use an if/then statement in the query? Right now- the Agent's code sits in whatever field they hold the position in. So I guess I don't understand how to tell it that when it's in this position x, it means that it's this variable in the junction/position table.
3) The Position table- I assume it has a primary key as the position- but do I need to link in the PropertyID as a foreign key at all? Or maybe the Agent Code? What all goes in the Position Table?

Sorry to bombard you- I'm just WAY behind deadline- and this is kind of complicated!