The correct ERD

[Enigma20xx]

I hope I can make myself understand correctly (I’m not English native speaker).

I’m trying to find out the correct ERD for this.

I have properties (Table), owners (Table)

ONE property can have MANY owners (no problem with that), but after this, there are many things that can change and I don’t know how to face them.

- Let’s say:

A boat has two owners.

Each one have 50% of the ownership.

First owner sells his part to a third one.

Now I need some kind of historical because owner 1 has been so from XX/XX/2015 to today and owner 3 from today until ...

- Another example:

John owns a house (100%), since XX/XX/XXXX until today that he sell a 50% to his wife Mary.

I have to set the new owner and that John doesn’t own the 100% of the house from now on (but keep track of the past situation).

 

Should I have a third table for the changing situations (dates and other changes like the percentage of property)? Would this be a join table between properties and owners?

Thanks for your help.

[Wim Decorte]

You need a third table for Companies/People.   Owners is just a join table between property and Companies/People.  In the Ownership record you can record start and end date, % ownership,...

[Enigma20xx]

Thank you very much Wim Dacorte.

I've tried to go a bit further, and apparently it works.

I have named the join table (Types Of Relationship), and I'll try to use it for many other relationships aswell.

Also added a filtered relationship to filter Owners only, for example, so I can get a VL that show only that kind of type, and I can use it with another table.

I think it work but I'd like to know your or others opinion.

Thanks again.

SampleDB.zip

[Enigma20xx]

Another twist.

Now we have a boat that can be owned by one or more persons. A VL based on a relationship can filter only the owners to be used in third tables.

Adding two more tables at the left side of the ERD.

Let's say:

A FLEET table and a JOIN table (fleet/proterties-boats-).

Placing the ID of a fleet in a third table, how can owners of any boat of that fleet be shown?

[Enigma20xx]

Thinking of the twist, I think is more a turn than a twist.

If I'm not wrong the new perspective goes to a conditional value list based on hierarchy. Sorry about that.

[comment]

Adding two more tables at the left side of the ERD.

I don't follow your description. Why two more tables? Based on your description, all you need is:

Fleets -< Boats -< Ownerships >- Owners

If you place a portal to Owners on a layout of Fleets, you will see all owners of any boat in that fleet.

 

Placing the ID of a fleet in a third table, how can owners of any boat of that fleet be shown?

You need to tell us what that third table tracks, and how is it related to the other tables (esp. the Fleets table).

 

Edited July 28, 2015 by comment

[Enigma20xx]

Thanks for your answer comment.

I’ll try to make a better description (guess the thing has become a bit different from the tread’s title).

In my solution all the properties from the same owner must be part of a “batch”, a group, I don’t know if that can be a correct name.
I’ll try with another example because I get lost with the definitions for boats .

Let's say we talk about flats (1 to 4)

Flat 1. Owners Peter and Mary.
Flat 2. Owners John
Flat 3. Owners Peter and Mary
Flat 4. Owners John

Now we have 2 batches:
Batch 1, flats 1 and 3, owners Peter and Mary.
Batch 2, flats 2 and 4, owner John.

In the sample file first attached I have a first part (properties--<TsOR>–People), and I can have a VL that brings out owners only, for example.

I think I can manage next step (making the batches (groups)).

The third table that I didn't explain well: Payments for example.

If I want to register a payment (only batch owners can pay), I don’t know how to relate paymentlines table with people (through batches/properties/TsOR(owners only)/people). All this to get a correct conditional VL and tags.

I think my attempt of explanation is even worse than the first one .

After all this mess I’ve made another version of my first sample file that I think will show better what I’m trying to accomplish.

Thank you all for your patience.

SampleDBv2.zip

[comment]

What exactly is a "batch" in real life? What/who determines that flat X belongs to batch Y?

[Enigma20xx]

A "batch" (now I see it wasn't a good translation) is just a group of properties owned by the same people/person.

Payments are invoiced to the group, and I need to choose/select which one of the owners of that group made the payment.

Edited July 30, 2015 by Enigma20xx

[Enigma20xx]

I'm sorry but I'm using tapatalk on my phone and is not easy...

Think of for example: attending a reunion.

The responsible of the building where the flats are, makes a reunion for owners only, to pay some reparations (the lift is always out of order).

1 owner = 1 vote.

If there's more than 1 owner, only 1 can attend to the reunion.

Mark has 2 flats (grouped as Group A (so only 1 vote)) and Bill and Shelly have 1 (grouped as Group B).

 

If we work with Groups, all flats have to be into one, even if there's only 1 flat into it.

Mark's vote "weighs" (I hope is the correct word) double, because he represents a Group that has two flats.

Managing the reunion, I have to set what groups have attended and which one of its owners.

Edited July 30, 2015 by Enigma20xx
Posted with Tapatalk non carriage return appear

[comment]

A "batch" (now I see it wasn't a good translation) is just a group of properties owned by the same people/person.

I am afraid that makes very little sense to me - in business terms. First, consider a situation where:

Property 1: Owners: Adam, Betty
Property 2: Owners: Betty, Cecil
Property 3. Owners: Adam, Cecil

Here there are no two properties owned by the same people. Would you view this as three groups of one property each? How about:

Property 1: Owners: Adam (50%), Betty (50%)
Property 2: Owners: Adam (80%), Betty (20%)
Property 3: Owners: Adam (50%), Betty (30%), Cecil (20%)

How many groups do you see here?

Next, and more importantly: these groups are derived from the Ownerships, not an entity of its own right. You cannot have a table of Groups and have your solution normalized at the same time.

 

 

[Enigma20xx]

As always, thanks for your wise indications comment.

In your first example I see 3 groups (including Cecil, the dead lion , just a joke).

In the second example I see 2 groups (props 1 and 2, and prop 3). *In this case % is irrelevant.

But anyway, rule for this is not any clear, lots of different oppinions about it.

 

The thing is, is it possible to have a conditional vl in the portal (showing paymentLines) placed in the payments table that can show those people who are owners of the property set in the portal?

Edited August 3, 2015 by Enigma20xx

[comment]

The thing is, is it possible to have a conditional vl in the portal (showing paymentLines) placed in the payments table that can show those people who are owners of the property set in the portal?

Yes - if either Payments or PaymentLines are related to Properties.

If you have a chain of relationships:

Properties -< Ownerships >- Owners

and you define a value list using values from a field in Owners, including only related values starting from Properties, then any table occurrence related to Properties can use this value list in order to select from owners of the selected property only.

 

Edited August 3, 2015 by comment

[Enigma20xx]

I appologise but I made a mistake when I said property in my last post, I meant Groups.

The thing is, is it possible to have a conditional vl in the portal (showing paymentLines) placed in the payments table that can show those people who are owners of the

property

set in the portal?

The contitional VL that you say, comment, is already done in the sample file.

What I meant was, is it possible to have a conditional VL in the portal (showing paymentLines) placed in the payments table that can show those people who are owners of the group set in the portal?

Thank you.

[comment]

I am afraid we may be going in circles here.

[Enigma20xx]

I am afraid we may be going in circles here.

Sorry but why is that?

I understand and appreciate all your advices, always have helped me a lot. I know it's important that things make sense, but sometimes things seem to have no that much sense, but it is what it is, and I try to solve it with my little knowledge and the help of you all.

There's no other structure. People have different relationships with properties, properties must be grouped by same owners (I don't make the rules). Groups make the payments, so in paymentLines I need to place 2 things, the group and the person that pays (that can´t be other than an owner). That is the reality. Maybe to accomplish this in FileMaker has to have another structure but I'm not able to see it...

That is why I ask what I ask.

Having this:

Groups -<join>- Properties -<Different types of realtionship>- People.

Payments -<PaymentLines

Is there a way to connect PaymentLines to Groups so choosing a GroupID any of its owners can be selected from a contidional VL? Made from a portal (PaymentLines) in Payments table. That's all.

Thanks once again.

[comment]

Sorry but why is that?

Because despite 15 replies in this thread, the nature of Groups is still not clear. Or the nature of your question.

If I ignore everything that has gone on before and take only this:

Having this:

Groups -<join>- Properties -<Different types of realtionship>- People.

Payments -<PaymentLines

Is there a way to connect PaymentLines to Groups so choosing a GroupID any of its owners can be selected from a contidional VL?

the answer is yes; if you link PaymentLines to Groups by GroupID, then a value list defined to use values from a field in People, include only related values starting from Groups, will allow you to select from people associated with the selected group through the given chain.