Relationships between more tables

[LasseJ]

Hi all

I have this relationship-problem, which is hard to explain (being a newbie, like me , but here it comes:

I have 4 tables "Person", "Company", "Post No" and "Booking"

"Person" and "Company" are related to "Post No" to autoenter Post District. That works fine.

Then I have "Booking" related to "Personal" through a matching ID.

What I would like to set up is "Booking" related to "Company" through a matching ID as well.

That means: Imported records to "Booking" are matched with IDs in either "Person" or "Company".

(The IDs in "Person" and "Company" are uniqe)

As its set up right now, its clearly wrong as the relationship between "Personal", "PostNo" and Booking somehow makes a relationship between "Company" and "Booking".

Confused? - Well I am. Hopefully the attached file can visualize what I mean..

Can anyone help?

Regards

Database.zip

[Søren Dyhr]

Why are records imported??

Try to explain, ....one company has many what, is it employees and each of them have their own bookings, or is a booking between a person and a company?

What makes it slighly weird is the word "Either" which suggests the one and same table instead of two - what happens if no persons in the company exists?

Well weird is perhaps the wrong word, but it isn't newbe stuff with recursive relational structures ...if this whats required here?

I've made you a template, since the recursive structure utilizes a selfjoin relation doen't it make sense to have the statusarea shown, hence the initscript. The means of navigating the solution provides might feel a little daft ...but it was simply what I came up with first.

It should be said that Lasse an I live in the the same country and due to the way address conventions are here, is it the way it's implemented in this solution.

Please notice that the fork in the relations def. actually points is opposite of what the ERD says. But if you turn your attention to JMO's site and enter "unnoticed" in the search field:

http://www.databasepros.com/resources.html

...will the matter be explained!

--sd

test.zip

[LasseJ]

Hi all

Søren, thanks for your input. Unfortunately I don't think its the right direction. Your questions are quite fair and it makes me realize I have to explain a bit better..

First I have two hidden tables, not included in the layout menu:

One Table, "Booking" means actually "Book Keeping" (sorry for my inaccuracy). That's where payments are imported on a regular basis.

Second Table, "Post No." is a table where Post Codes, related Post Districts and countries are. That gives me the benefit of only having to put in Post Codes when making new cards in "Company" or "Personal". (As Søren knows).

This relationship works fine, but somehow influences on the portal relationship in the two visible tables: "Company" and "Personal".

The two "visible" tables "Company" and "Personal" are actually very much alike. I have them separated in to tables, because I thought it would be a easy way to distinguish them from each other when handling the data. Maybe its here my mistake is?!? I switch between these two tables with a "go to layout"-button.

Whenever I import new payments into "Book Keeping" the data imported into the ID-field will either match the ID in "Personal" or "Company".

I have attached a new version of the database Hopefully that also clarifies a bit.

Thanks for your help in advance!

Regards

Lasse

Database_v2.zip

Edited March 8, 2007 by Guest

[comment]

I think you should consider putting Persons and Companies in a single table.

Alternatively, you could define two separate relationships, one between Persons and Book Keeping, and another between Companies and Book Keeping (using two occurences of the Book Keeping table). But this could get quite complex: first, you would need to make sure no IDs are shared between a person and a company; and if you want to view related data from both directions you will need to switch to anchor-buoy model (more table occurences).

The Post Codes table also needs to be split into two occurences, along the same lines, instead of connecting between Persons and Companies.

[Søren Dyhr]

Alright where my original model fails is when bookkeeping always are tied to a person, the alternative is as comment suggests and along the lines of this:

http://www.fmcollective.com/2006/12/postdesign_cauc.html

I'll rethink such a solution! Perhaps you could expand a little on this:

I have them separated in to tables, because I thought it would be a easy way to distinguish them from each other when handling the data

Both Michael and I feel it ought to be one table, but perhaps it's like the fmcollective that the two found sets are treated very different.

We need to know something about what's done to each set of records, maybe what keeping them in one table is that you havn't fully embraced this scriptstep:

http://www.filemaker.com/help/Script-Steps72.html

...opposed to stacking requests, that have a more OR'ish behaviour?

But you might if the attributes differs too much use the idea of the autoenter value is on foreignside the many-side, say like the attached image ...here is a plentora of addresses posible for both companies and persons, but each bookkeeping goes to one specific address.

--sd

Edited March 9, 2007 by Guest