Filemaker DBMS

[jhamilton]

Hello,

I'm trying to understand how Filemaker is managing it's tables and relationships. I've seen examples of Filemaker databases where relationships are created following Codd's relational database design. I've also seen examples of databases with seemingly lots of data duplication and non-traditional key placements. I'd appreciate it if someone could explain to me how Filemaker is dealing with data integrity.

Thanks.

[comment]

That's a rather wide question. Filemaker is a tool for developers. The design of a solution and its data model is up to the developer, not the tool.

If you see a solution that doesn't follow normalization rules, it could be that (a) the solution was denormalized purposefully in order to increase performance or to implement a feature that couldn't be implemented otherwise, or (: the developer doesn't have a clue.

[jhamilton]

Thanks for replying. I've attached an image of the relationship menu from an example database. What is the rationale for the division of attributes? Why is there so much replication? I'd really appreciate clarification on the database structure going on in Filemaker. Also, why does Filemaker name the database file the same as the first table, as if you'd only have one table and want it to be named the same as the database file?

Thanks.

[comment]

I am not the author of that, so I can only offer a few pointers:

1. The relationships graph is NOT an ERD. The "entities" on the graph are not tables, but table occurrences (instances of the base table).

2. You are likely to see what may seem like duplications on the RG, because Filemaker will allow only a single relationship between any two table occurrences (including indirect relationships). This ensures that there can be no ambiguity when referring to a TO from the context of another TO.

3. Many developers subscribe to the "anchor-buoy" model of organizing the RG - see:

http://fmforums.com/forum/showtopic.php?tid/196775/

why does Filemaker name the database file the same as the first table

4. It's the other way around: when you create a new file, it will contain a default table named the same as the file. You can easily rename the table - but before you do, it's gotta be named *something*.

[jhamilton]

Will the system work if I create relationships in ERD fashion? Why can't an entity have a relationship with more than two occurrences? Are unary relationships possible?

Thanks for the help.

[comment]

Will the system work if I create relationships in ERD fashion?

The system will not let you create relationships in ERD fashion. Here's a simple example:

Invoices -< LineItems >- Products

Say you want a second relationship between Products and LineItems, filtered by a date range (i.e. showing only items sold between two dates). You MUST add a new occurrence of the LineItems table (or the Products table) in order to create this relationship. Otherwise, when you call from the context of Products for the count of related LineItems, there'd be no way of knowing if you want the count of all items having the same product, or just the items within the date range.

Are unary relationships possible?

Yes, but to implement them you must place two distinct TO's of the same table on the graph (as with any self-join).