Relationships and Multiple Table Instances

[spankalee]

I have a pseudo object-oriented inventory setup in FM7. There's a base Products table, and then tables for specific types of products that "extend" the Products table. The tables are related through an ID. This causes all the sub-tables to be related to each other through the Products table.

They really shouldn't be related, but I'm not sure if it's a problem at all. No product will even exist in more than one of the sub-tables, so even though there's a relationship defined, there won't be any data that actually establishes it.

The only problem this has caused so far is pretty minor. I have some extra tables holding attributes of certain products, things like colors. A few of the sub-tables use the colors table through a relationship, but because the sub-tables are already related though Products I have to create separate instances of the colors table, otherwise there would be two relational paths.

I'm also worried that having all these extra relationships could cause performance issues when the DB grows. But that may not be a problem really.

So I've been thinking of creating a separate instance of Products for each sub-table so that there wouldn't be those extraneous relationships. I'm worried this could cause some problems as well thgouh. One problem I see is that tables that relate to Products, but aren't "sub-tables" (so they need to relate to all products, not a specific type), would need to be linked to yet another instance of Products, and wouldn't be reachable from one of the sub-tables.

I know all that may have sounded confusing... I'm interested in anyone's thoughts on the issue.

Thanks,

-J

[Ender]

I'm not sure how your structure fits in with the theory of normalization. Designing a database is different from designing an object-oriented program.

My feeling is like-data should be kept in the same table; so this might mean only one sub-table would be necessary, even if some fields aren't used for all records. There could be different catagories of sub-table data, where the category is used as part of the relationship to Product. Each category could be displayed in a separate portal, or a value list of categories might be used to give users a choice of which related product details they want to see in the portal.

If you can provide more details on what the different sub-tables contain, it might clear things up.

[supergrug]

I am having a similar issue. I have drawn up an ER diagram, but am not entirely sure how to implement specialised entities. EG I have a PERSON entity that is specialised into STAFF, MANAGER, GUARANTOR.

It would be easy to make a separate table for each of these roles that include the attributes in PERSON, but this isn't ideal (or correct relationally as far as I know). This would cause redundancy if for instance an instance of STAFF was also an instance of GUARANTOR.

Originally I assumed you would relate the STAFF, MANAGER and GUARANTOR entites to the PERSON, but this creates what I can best describe as circular relationships when these specialised entities are linked to their functions.

FileMaker then wants me to make a duplicate table for reasons I don't understand.

[Ender]

Hi supergrug,

Your question is similar and I'd like to understand the thinking behind this.

It seems like Staff, Manager, and Guarantor are categories of Person. What is going on in those tables that makes them distinct and what are the relationships to Person?

[spankalee]

Ender,

I think in some ways this design is better normalized that leaving many fields blank. I'd have to bust out my DB design book to be sure, but if every product of type "tile" leaves a bunch of fields blank every time and uses fields that no other product type uses, then the table is not normalized.

In my case, a lot of product types have non-overlapping fields. Tile have width, height, area, thickness, shape. Loose stone has weight and grade. Mosiacs have all kinds of fields for design, types of rocks, shape, size, etc.

What I've don is take all the common data like code, description, pricing, units, weight, etc. and put it in a products table. This way it's easy to look at a list of all the products no matter what the type. Then If you want to view specifics I have a scripted button that takes you to the correct layout.

What I really like about this is that some of the common product data is generated in different ways depending on the type of product. Tile products have 3 attributes that combine to make up the product code: style, material, and grade. Those fields relate to tables. Other attributes are determined by values in those tables like dimensions and weight and description. In the Tile Product layout I have drop down boxes and script that calculate and fill-out the fields properly.

I think it's a nice design and it's working quite well. Adding new product types is pretty easy. Also I believe that this design is fully normalized. The only thing that worries is FileMaker's specific way of handling relationships. Based on my relationships is would appear as though the TileProducts and SalesMaterials tables are related through product code and the products table, but they really aren't. No TileProduct and SalesProduct will ever have the same product ID.

What I don't know is whether or not FM is doing indexing and joins based on relationships that aren't really relationships and slowing things down.

I'm attaching a snap of the relationship diagram...

[supergrug]

The thinking is to follow correct ER modelling with the aim of completely normalised data. The implementation of these models in FileMaker (or FileMaker + Me) is the problem.

I have two easy (but not ideal or correct for a proper modern database) options:

1. Have only 1 table relating to people, and have all fields relating to GUARANTOR, STAFF etc in this table. This means most fields for STAFF will be empty as the GUARANTOR fields won't apply.

2. Have a table for each role that includes common fields. This causes redundancy when a person is both a GUARANTOR and STAFF.

Like I said, neither of these methods is a correct implementation, despite many people probably taking one of these approches.

The other posters diagram looks OK (though a little small so I can't be sure). Where mine gets more complicated is that after linking PERSON to the subclasses (which is fine), linking these subclasses to their functions ie STAFF works for PRACTICE, GUARANTOR insures PRACTICE, a circular relationship is created ie PERSON -> STAFF -> PRACTICE -> GUARANTOR -> PERSON which is what FileMaker doesn't like. It tries to get me to make a different instance of PERSON for each role, which defeats the purpose o f consolidating common data into a single PERSON file.

If you want to understand why people model using this approach, you would need to go back and have a read of a good database textbook.

Thanks for your input

Simon

[spankalee]

supergrug, you can download the attachment to see the full size image; it's the forum shrinking it down.

If you look at my diagram you'll see that I have multiple instances of the Units tables because of exactly what you're talking about: FileMaker complains about circular relationships.

It doesn't defeat the purpose of having a Person table though. The same records are available from each table instance as long as they match the relationship. It's really just a way of telling FileMaker which relationship (or chain of relationships) to use.

My diagram has actually gotten more complicated today since I needed to add relationships between some of the product sub-classes and the Stones tables. Again, I had to create new instances to avoid the circular relationships.

[Ender]

spankalee,

Your diagram and explanation was helpful for seeing what's going on. It sounds like the relationships between Products and sub-tables are all 1:1. While 1:1 relationships can exist, they can usually be eliminated. However, you have pointed out reasons to use them in your case. LaRetta has started a thread about this very topic:

http://www.fmforums.com/threads/showflat.php?Cat=&Board=UBB17&Number=112586

You don't need to worry about the performance being worse in this design, unless you are performing sorts on fields in related tables. Using additional table occurances certainly isn't a problem, except that it makes makes the structure more complicated.

[Ender]

supergrug,

It's still not clear what kind of data is in your tables and how they relate to Person. I don't know what a Guarantor is. Are all Person records Staff too? Or is Staff referring to the Persons under a Manager's supervision?

For each entity, can you say something like:

Each Staff record is related to 1 Manager, and each Manager can have many Staff.

These are the kinds of questions I like to ask for each possible relationship.