Specialized Tables versus Multi-purpose Tables

[crayfish]

Not sure if this the right place but was curious about people's thoughts on specialized tables versus multi-purpose tables. 

 

For example, let's say you have a database with a number of different tables (Table_A, Table_B, Table_C, Table_D)  Table_A needs to have a many-to-many relationship with Table_B, as well as with Table_C, and with Table_D so it needs a join table between each of them.  In the past, I would create three different join tables, each for the specific join relationship (Join_AB, Join_AC, Join_AD).  The only fields in the Join tables would be its own primary ID, Table_A's foreign ID and the foreign ID of the table being joined.  

 

What if instead of multiple join tables, you just create one join table and add a field to it that explains what relationship that particular record serves.  Then in Table_A you create a global field for each kind of relationship and create table occurrences for each relationship connecting them as follows. 

 

 

 

Table A has the following records:

 

The Join table has the following records:

 

Therefore....

 

Record 1 of the Join table joins Record 1 of Table_A with Record 5 of Table_B (since <<Table_A::relationship_Table_B>> == <<Join::relationship>> ).

 

Record 3 of the Join Table joins Record 2 of Table_A with Record 4 of Table B (since <<Table_A::relationship_Table_C>> == <<Join::relationship>> ).

 

 

Is there any downside to doing this beyond losing flexibility in terms of having data in the join tables that explains each relationship?

 

And if that becomes an issue, what if you do the following:

 

 

Within the "Join" table, add some new fields:  text1_Description, text1_Value, num1_Description, num1_Value, text2_Description, text2_Value, num2_Description, num2_Value

 

 

 

So for example, in relationships between Table_A and Table_B the Join record uses text1_Value to store the "name" of the relationship.  For relationships been Table_A and Table_C, tex1_Value is used to store the "team" of the relationship.

 

----

 

Any downsides to this technique?  Obviously, you could get stuck having to create endless new fields in the Join table if you start needing more field values describing various relationships.  So this would only make sense if you knew the Join table only needed the primary and foreign keys or if you knew that the Join table would only need a few extra fields to describe the nature of the relationships.

 

The major upside to me (and why I'm considering this) is needing a lot fewer join tables (and therefore fewer layouts as well).  In the solution I'm working on that has 30+ tables with a ton of many-to-many relationships this design seems very attractive.  Or perhaps I am just shuffling complexity around?  

 

Hope my explanation made sense.  Any thoughts would be appreciated.  Thanks!

 

 

 

[LaRetta]

Hi Crayfish,

 

What is the purpose of tables B, C and D?  Combining information from three different purposes will add complexity down the road since most reporting would take place in that singularly now-combined join table and you will have to always filter out the other tables.  That would be easy from developer perspective using constrain (or omit during the report find) as well as security but it puts more work on the system to always remove what probably shouldn't have been placed there to begin with.  As I was once asked by a great developer, "why put things together only to take them back apart?"

 

Two extra table occurrences is very little (probably nil) system stress and layouts are cheap.  There are times to put things together ... if they are similar records ... but generally it is best to split to finite level.  Tables B, C and D obviously are different entities, right?  Then their join records are different from one another as well.  In today's world our tables should be narrow (fewer fields) for mobile and WAN use and if you design only for desktop, you are designing legacy.  Another consideration is table size (number of records).  Join tables tend to get very large and you will have 3 times the volume.  This may be okay depending upon type of data being held.   If this involves only a few fields and records (a smaller functionality type situation) or to hold miscellaneous odd data then it sometimes will work.  

 

Others may see things differently and I remain open as well.  I commend you for exploring alternate configurations and asking the difficult questions.  In FileMaker there are few absolutes and many ways of accomplishing the same task.  I believe the key will be the purpose of those tables.

 

BTW, could you please update your profile with your OS and version?  It will help us when answering since different versions and OS can influence our answers. 

[Fitch]

Why not use one table and do everything with self-joins?

[comment]

Any downsides to this technique? 

 

Well, one thing that jumps out is that the relationships are unidirectional. I believe the implications of this would be much more obvious if the example were less abstract. But just consider producing a report from the AB join table, when you cannot place fields from A on the layout.

 

 

 

The major upside to me (and why I'm considering this) is needing a lot fewer join tables (and therefore fewer layouts as well).

 

That's not much of an upside, is it? I mean the number of TOs you'll need to have on the RG is the same, and layouts are easily tucked away in a folder.

 

 

 

Why not use one table and do everything with self-joins?

 

Why not use one field and do everything with scripts? 

[David Jondreau]

Your example is too abstract to give actual advice.

[Fitch]

Why not use one field and do everything with scripts? 

 

Scripts? Why not use parameters and do everything with one script?

(I thought it was obvious I was joking, but maybe not.)

[bcooney]

Ha! We used to joke about creating a solution with 1 field, 1 layout and 1 script. I'll leave that to you, comment!

[David Jondreau]

With one record. And the field is a global!

[comment]

Let me point out to you that the most disrupting factor in a database (no, I am not talking about the users) is data. The more data you have, the more complex the database needs to become, and performance takes a dive.  The best-performing database is the one with no fields, no layouts and no scripts. Without fields to provide shelter, there's no room for that pestiferous data to insinuate itself into our camp and soil our immaculate solution with its dirty claws. Say no to data!

[LaRetta]

Indeed!  Data is the true culprit!  Vile it is ...

[Josh Ormond]

Isn't that what some Wordpress plugins do? LOL

 

Ha! We used to joke about creating a solution with 1 field, 1 layout...

[Tjebbe van Tijen]

Apart from the jokers here that missed your valuable point and make their lack of understanding public ....

 

When it comes to more complex databases & tables relationships, this question does arise.

In my experience it is not one solution against another (specialised tables versus multi-puprose tables).

One mostly needs a combination of both. Analysing the data you will need to process will show you at a certain point in the design process that several 'specialised tables' do have many fields/data types in common, hence they could well reside in one table with an extra field indicating the usage of the table. Example combining the data on persons and organisations in one table as the build up of personal names and organisation names do have many things in common. In the past I had specialised tables with many specialised fields. Now I tend to put all those details in a multipurpose table - with only a few fields - that links to the specialised table.

The need for many fields often occurs because of multiple 'data versions' for one record element (field in a record).
Example I do international bibliographical databases in many languages and also different scripts (Latin, Chinese trad. Chinese simpl., Japanese, etc.) That would end up with twenty or so fields for name variants. Of course one can make a more simple table that either links to itself for variations. That is a 'many to one relation' solution, and it brings down the number of fields in that table, one only has to make a level/indicator what particular 'name form' resides in one of several records all pointing to the same person. 

In my practice I do use often use also a 'join table' that allows me to make 'many to many relations'. 

Join-tables do allow for a design strategy with 'multi-purpose tables', as they combine two (or more) tables with their 'prime keys/unique record indicators'. 

I am working now on a system whereby I try to have all relations between the set of tables of database + rela external databases and their tables link through only ONE join-table (there can of course exist many specific occurrences of that join tables in the Filemaker relation system). My thought being that this way of working forces me to come up with solutions that are applicable to more than the usual 'specific relation'. It also will come handy when building some sort of search function through all the data elements of my system.

The nice thing (you did notice that also I read here) that a 'join-table' allows you to further specify the meaning of that specific relationship.
Whereas simple links between tow elements most often do not say anything about the specifics of that link, with a 'join-table' one can do that, with one or many more elements if wished. For myself I call that 'attributes' of a relation. Say in a bibliographical database it could say in a join table of a book with a person name, the relationship with a person's name has here the value of:
- author
- co-author
- reviewer
- subject (in the book)

etcetera...

Such attributes ir if you like call them 'ratings' do come of course from a controlled language value list (in my case I use a dictionary database with many specialised tables to keep control over a wide range of terms and codes.

Last I post here a screen shot from my new person/organisation database in development.....

I think I rename the specific fields also with 'data01', data02' etcetera and introduce an extra field 'data01field' that will with each record tell what is the meaning of that the 'data01', etc. fields. In this way I can use the same table for both persons and organisations.


 

[Josh Ormond]

LOL...it is in no way a lack of understanding.  We are well aware of the Attribute-Value model. And it does work well in FM. Matt Petrowsky demos some of this on his FileMakerMagazine.com when he walks through the separation model using his Karate app as an example ( I think that was the set of videos ).

 

The jokes were from the extreme abstract nature of the post...not entirely the idea. It would have been easier to give an opinion or thoughts had it been a real-world example.

[Tjebbe van Tijen]

dear Moderator maybe LOL can be classified as lack of courtesy ... I am under the impression that I did understand the initial question.
I would appreciate if you supply me with direct URL references to the things you mention in your reaction to my post.

[LaRetta]

Hi Tjebbe,  relax ... there is no need to get defensive.  You started this thread in a tone of putting us down when you do not know us, you do not know our training or backgrounds ... between us we have 70+ years of FileMaker experience as professional developers.  Josh is probably most easy-going person on ALL the forums I'm on.

 

So let's just talk facts, okay?  I have read your post with great interest but join tables are not new; in fact nothing you have said is new at all. I agree with some of your post and I disagree with other parts.  I am interested in your ideas so please show us a bit of courtesy as well, okay?  We all are here to help each other and these interactions should be (and usually are) very fun as well as informative. 

[Lee Smith]

dear Moderator maybe LOL can be classified as lack of courtesy ...

A little levity does not constitute a lack of knowledge, experience or assistance. Nor is there a lack of courtesy. I think you have over reacted, and should step back before posting a rant such as this.

 

Our forum is here to help all levels of skill, and we did not get our excellent reputation from allowing personal attacks, weather they are in a general sense or directed at one individual.

 

Please reread this link Terms of Service

[Josh Ormond]

I believe this is the video where he talks about Attribute-Value stuff. In this instance he uses the Web Viewer as the actual display medium.

 

http://www.filemakermagazine.com/videos/the-separation-model-part-5

 

dear Moderator maybe LOL can be classified as lack of courtesy ... I am under the impression that I did understand the initial question.
I would appreciate if you supply me with direct URL references to the things you mention in your reaction to my post.

 

 

Awww. Thanks. 

 

Hi Tjebbe,  relax ... there is no need to get defensive.  You started this thread in a tone of putting us down when you do not know us, you do not know our training or backgrounds ... between us we have 70+ years of FileMaker experience as professional developers.  Josh is probably most easy-going person on ALL the forums I'm on.

[Fitch]

As the initial "joker" I'm sorry if I offended. The point I was trying to make was: simplicity is in the eye of the beholder. Without knowing more about the actual requirements of the database, my reaction was that crayfish was adding complexity, not reducing it.

[Josh Ormond]

I don't see much need for apology. The original question was a good one for discussion. Albeit, a little vague on the real life application. I was hoping the OP would come back with a real world example. That would have been so much easier to discuss. In lieu of that, lite-hearted discussion makes the forums fun.

 

As the initial "joker" I'm sorry if I offended. The point I was trying to make was: simplicity is in the eye of the beholder. Without knowing more about the actual requirements of the database, my reaction was that crayfish was adding complexity, not reducing it.