Sold house - buyers - sellers

[El_Pablo]

Hi,

I want to represent a structure of sold houses with their buyers and sellers.

A building can be sold to buyers (Eg A couple)

A building can be sold by sellers (Eg : Brothers)

A buyer or seller has an address and is represented by a person.

I have already done the structure to represent sold houses with buyers and sellers, but I was still wandering how other DBAs would develop the structure.

Here's my structure :

The structure works well, but there's a small problem. The sellers and buyers table have exactly the same structure, it's more like a maintenance problem. Is it possible to use a single table for both entities?

[Vaughan]

Buyers can be sellers and sellers can be buyers. I'd suggest one table for people.

Consider also that houses can be owned and sold to *multiple* people. Do you want to allow for this?

I like your "Sales" table, it should have information about the sale such as the price. I'd also have a table for "Properties" that contains information about the property such as address, type, etc and link this to the Sales table. That way you can over time track information about particular properties.

I'd suggest join tables "Owners" and "Buyers" each of which link to a "People" table. Technically companies and other legal entities can buy and sell property too, so don't assume that the buyers and sellers will all have first and last names.

The relationships would look something like this

Properties --> Sales --> Owners --> People_Owners

Properties --> Sales --> Buyers --> People_Buyers

[El_Pablo]

Actually, this was tiny bit of the project. There's already a "properties" table link to "sales".

I was only wondering for this part, if there was a better way to represent "sellers" and "buyers" since each table is exactly the same. If I add a field in "buyers", i will need to do the same for "sellers". In OOP, we could use inheritance, but in FM I don't think it's directly possible.

The project will be use for building appraisals. I've built the project from ground up. There are more than 470 TOs.

[David Jondreau]

If they're the same, you only need a single table with an extra field designating whether that record is for the buyer or seller.

[David McQueen]

Suggest a people table (associated address table etc)and a sales table.

Sales table has vendor ID and Purchaser ID. Using FileMaker, these can be indexed text fields and hold compound values to handle multiple vendors and purchasers on any particular deal.

People are never flagged as vendors or purchasers. they take their role by where their ID is in the sales table.

Just a thought.

Dave

[El_Pablo]

@D J:

Then I will need two fields in "sales" that will make the difference between a seller and a buyer. I don't like the idea that a sale should know stuff about their sellers and buyers.

This method create a dependancy between sales, buyers and sellers.

Don't you think so?

@David McQueen:

Sales cannot have vendorID and purchaserID because a sale can have multiple vendors and purchasers (Eg You could buy a building with your brother and the building is sold by a couple).

[David Jondreau]

I don't see what's wrong with a sale knowing who the buyer and seller are. Could you elaborate on why that's a problem?

To elaborate on what me and DM are saying...

You define a BuyersSellers table which is simply the PK of the Sale, the PK of a Person, and their Role. You can have multiple records for a Sale with the designation of Buyer or Seller.

This assumes a simple Person-based representation of entities. You probably have, and should have, a more complicated one that takes into account Companies, Associations, etc.

And I guess that's the other way looking at it...Define Parties table to collect (via another join) all the PKs of the Persons buying and all the PKs of the Persons selling and use those two PKs in the Sales table.

PS If you have 470 TOs and you haven't addressed the Party issue, what's going on in your graph? How many tables do you have?

Edited January 15, 2010 by Guest

[El_Pablo]

I understood your model from the beginning. I was just pointing out some cons from a design pattern point of view.

Don't worry about the 470 TOs I was just asking for other opinion. FYI, there are like 150+ tables separated in 5 files + the presentation layer for this project (called the separation model in the FM community).

The underlying question was how the FM developpers model inheritance (class diagram 101) in the relatonships graphs.

Concerning inheritance modeled in FM, I always had a dilemna of what was the best way to represent it since I know 3 ways.

1 Creating different entities for each class. (Buyers and sellers). Cons : Maintenance.

2 : Creating a single entity with a type field (buyersSellers). Cons : No direct conditionnal value list in FM.

3 : Creating subclasses for each entity (which do not apply well in FM).

Each method has there pros and cons.

Thanks

[El_Pablo]

I guess your model should look like this?

I attached the small example file, if you want to add your POV.

buySell.zip

[David McQueen]

@D J:

@David McQueen:

Sales cannot have vendorID and purchaserID because a sale can have multiple vendors and purchasers (Eg You could buy a building with your brother and the building is sold by a couple).

Not so.

You can have a key field in sales which is a compound key. Lets say "Vendor" is the field name and lets say there are multiple vendors, the contents of that field would be something like this:

vendor1

vendor3

vendor27

as a return delimited list. A portal based on the values of that key to people would show all three vendors in the portal.This is why originally I suggested using keys of the field type text. It gives you that flexibility.

My thought was that you were trying to build in effect a family tree around real estate sales where the behaviour of both people and properties over time was of interest.

HTH

Dave

[El_Pablo]

A sale is kind like of a picture of a house in time. This is why there's only two parties sellers and buyers.

I guess you meant composite key. Are you saying that I should use a kind of array as a key in the sale table? I've never heard that we could use a list as a key at least in the SQL world. Or I don't get the delimited list part.

The basic relationships are two many-to-many between persons and sales. A house (called a sale in our case) can be bought and sold by multiple persons at a given time.

That's why we need a sellers table and a buyers table. To split the many-to-many relationship.

If we go further, a house can be registered sold many times.

Maybe you could explain the delimited list part with the file I attached.

[David Jondreau]

You CAN use a pilcrow delimited field though it's discouraged. You should use a join table instead.

I can't really say more about what model you should use, because it's tied closely to your interface and your current graph conventions.

For example, I don't think I would split buyers and sellers into two TO groups.

[bruceR]

You CAN use a pilcrow delimited field though it's discouraged. You should use a join table instead.

I can't really say more about what model you should use, because it's tied closely to your interface and your current graph conventions.

For example, I don't think I would split buyers and sellers into two TO groups.

Agreed. In this case the join table is "Parties" (people associated with the transaction) and one of the fields is "Role".

A portal or report could be designed to show all related parties; or only buyers; or sellers; or ... etc.

[David McQueen]

I guess you meant composite key. Are you saying that I should use a kind of array as a key in the sale table? I've never heard that we could use a list as a key at least in the SQL world. Or I don't get the delimited list part.

The basic relationships are two many-to-many between persons and sales. A house (called a sale in our case) can be bought and sold by multiple persons at a given time.

That's why we need a sellers table and a buyers table. To split the many-to-many relationship.

If we go further, a house can be registered sold many times.

Maybe you could explain the delimited list part with the file I attached.

Yes, you can use an array in the form of a return delimited list within text or text based calculation field. One of the posters indicated that it was "Discouraged". One of the reasons being that if you overrun the ability of your text field to hold data you will lose part of the data from that relationship. Another reason being that taking the structure to another database would be problematic.

I was suggesting it here as there is a very finite amount of information for any particular sale.

My main concern was the use of two tables for purchasers and vendors. Every purchaser can be a vendor and vice versa.(a) You should not have to enter these names twice. ( In following a chain of sales, you should be able to set up relationships that would follow the actions of any particular person. You cannot do that with two separate tables. It is a structural limitation to the model.

[comment]

Yes, you can use an array in the form of a return delimited list within text or text based calculation field. One of the posters indicated that it was "Discouraged". One of the reasons being that if you overrun the ability of your text field to hold data you will lose part of the data from that relationship.

That is hardly an issue in version 7 and higher. However, there are other issues to consider. The most significant one, IMHO, is that by using a multi-key all sellers/buyers become equal and indistinguishable members of their group.

In real life, however, A can sell a property he owns single-handed to B and C, where B purchases 60% of the ownership and C the remaining 40%. Without a join table, there is no room to record the attributes of a specific join.

[David McQueen]

Yup. It falls at that point.

Again, the main concern was separate tables for vendors and purchasers. There may be good reasons for it, but it is a limiting structure.

[David Jondreau]

Two tables is limiting, three is expansive. If you have Parties, Sellers, and Buyers, you're in good shape.

But implementing a party-based model can be tricky.

Edited January 17, 2010 by Guest

[comment]

What exactly is the difference between a seller and a buyer - other than a minus sign?

[El_Pablo]

@D J

For sales part, there's is no need for a party-based model, since there are only sellers and buyers. I've already implemented such model in another project and your right to say that it can get quite tricky.

@D.McQueen

I separated the tables because they were representing two separate classes in the class diagram. They were then two disctinct entities. The main problem when separating the two tables is the maintenance, since both, for now, are exactly the same.

@comment :

For now, there is no really difference between both.

Take note that a seller (or buyer) is a person or a legal entity (eg : Bank). But this kind of information is not useful for the system.

We need to verity speed of execution versus usefulness of this extra relationship.

PS : Take a look to different method of modeling inheritance. http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/09/30/how-to-model-inheritance-in-databases.aspx

Edited January 17, 2010 by Guest
Added link

[bruceR]

Two tables is limiting, three is expansive. If you have Parties, Sellers, and Buyers, you're in good shape.

But implementing a party-based model can be tricky.

Party-based tricky? How so? Also, your list of tables is incorrect; this is a choice, not all-of-above. Party table ( w type = buyer or seller as one attribute) OR: Buyers table + Sellers table.

[bruceR]

2 Creating a single entity with a type field (buyersSellers). Cons : No direct conditionnal value list in FM.

Thanks

Explain please. No readily apparent reason for this conclusion.

[David Jondreau]

What exactly is the difference between a seller and a buyer - other than a minus sign?

There may be different attributes Pablo wants to track. I don't know what his specs are. I had in mind credit score, stated income, etc for a buyer. Dunno if that's realistic.

@comment

For now, there is no really difference between both.

Guess not.

Party-based tricky? How so? Also, your list of tables is incorrect; this is a choice, not all-of-above. Party table ( w type = buyer or seller as one attribute) OR: Buyers table + Sellers table.

To me the choice is Party, Buyers, Sellers, OR Party. If there's a threshold of difference between Buyers and Sellers you have three tables. If they're relatively the same, just one.

To me, party-based (aka Graham-method) means you have one table, Party which keeps the PK, and the info common to the subtables plus extra tables for the other non-common attributes but which use the same key for relationships. I also understand it to mean these TOs are organized in one TO group, not using anchor-buoy.

Is that how you're using the term?

Explain please. No readily apparent reason for this conclusion.

In my experience, creating a value list is a little simpler and, under certain conditions, loading it is much much faster, when using an index from an entire table rather than filtered through a relationship.

Edited January 18, 2010 by Guest

[comment]

PS Take a look to different method of modeling inheritance. http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/09/30/how-to-model-inheritance-in-databases.aspx

The last strategy mentioned in that article is not really an option in Filemaker, because it doesn't do union.

[comment]

There may be different attributes Pablo wants to track.

I am not 100% sure what he wants to track - but in general, if you're tracking ownerships, then today's buyer is tomorrow's seller.

[El_Pablo]

The users who will use the system, don't really need to know much info about the sellers and buyers of a house. They just need to check if the sellers and buyers live at the same address. E.g. A guy who has a girlfriend for a while and she decides to move in and later buy 50% of the house. If this happen the user cannot take this sale as a comparable. Since the sale price could be biased then not reprensenting the real market value.

For those who are wondering what are comparables? These are sales which are used in a report to compare sale price to appraise the value of a subject house.

BTW, I attached an address table to buyers and sellers.

Edited January 18, 2010 by Guest

[El_Pablo]

* El_Pablo Said:

PS Take a look to different method of modeling inheritance. http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/09/30/how-to-model-i nheritance-in-databases.aspx

The last strategy mentioned in that article is not really an option in Filemaker, because it doesn't do union.

I noticed that a few persons prefer the first stategy to the others (merging buyers and sellers). From my personnal point of view I prefer the second one.

[comment]

I believe it depends on the purpose. I would certainly adopt the first strategy (i.e. a single table) when the subtypes do not have any specific attributes (as seems to be the case here).

[bruceR]

You can also use attribute groups.

[El_Pablo]

Interesting explanation from another thread.

From LaRetta:

I took over a design where there were 22 files. Each file was a sales rep and contained their customers (these were all Act databases). There were also files for manufacturers, jobbers, retailers and employees. And many of these persons were MULTIPLE types, a Manufacturer who bought products as well, or worked as a jobber in certain situations and so their information was entered in all relevant files.

If William Tate called, new reception would frantically ask, "who are they?" They didn't want to ask the person (and rightfully so). So they searched the manufacturer file then Salesperson A's file then ... you see the picture? Sure, in FM we can script the searching through all the tables/files for this person but that person is sitting on hold on the phone while this is happening. And a new receptionist might be searching for someone named Bill Kaiser only to find out that they are the CEO of their very own company!

People are unique. We should look in only ONE place for a person. If receptionist gets a call from William Tate, they should look in one table and it should show that William Tate is a manufacturer of one of our products but he actually buys our products as well) and it then should say who the sales rep is and so forth.

You can have aux fields off of the persons table (which contain only the unique information pertaining to each type) or you can have fields all in one table (unused fields are cheap); only use another table if you won't need to filter by them. If you use a join table with TypeID and PersonID, that will be the ONLY place to look to know every role a person might hold.

When needed, I might switch new relationships using this method since it seems easier to implement in FM.