1 or 2 tables?

[tobbesfilemaker]

Hello, I`ve got a table with pdfs(containers), some of these are related to another table but som arent, should I have all pdf.s in the same table or should I separate them so I have related pdf.s in 1 table and unrelated in another table?

 

Really appreciate your help.

[Lee Smith]

Your description doesn't say why some are related or not, so what is the difference?

It sounds like one table is all that is required, why not attach a copy of your file, or a mock up.

[tobbesfilemaker]

I`ve got 1 table, houses.
One house can be related to many documents ( pdfs. ).

 

But I also want to be able to store normal documents that arent related to anything, just so its there, so I know I have it somewhere.

This is 1 table right?

[Wim Decorte]

Sounds like 1 table: documents is an entity, regardless of where it comes from / belongs to

[LaRetta]

As Wim suggests, a single table would be best approach.   The documents which are related will carry the HouseID and those that aren't will hold an emptyHouseID.  You will need to provide a layout based upon Documents so those PDFs without parent IDs can be searched and found or you can provide a portal based upon a relationship to those documents without a HouseID by relating as such:

 

Houses::HouseID > Documents::HouseID

 

This will allow all unassigned documents to appear on every parent record for selection or viewing.  You would still need another portal relating Houses::HouseID = Documents::HouseID to list documents assigned to that specific house.

[comment]

Y'all are very brave to answer this based on such scanty information. I would ask at least three questions before even beginning to form an opinion:

 

1. What, if any, would be the difference between the two tables in terms of fields required to describe a document (apart from the foreign key field to Houses)?

 

2. Would a document ever need to be moved from one table to another?

 

3. When searching for a document, would you expect to find matching records from both types of documents?

[LaRetta]

In this case, Michael, I didn't feel we had 'scanty information' at all or IOW, the answers to your questions (or any questions) would make little difference in my response:

1. Empty fields take little resource although if there were many different fields, another table holding those different fields might be warranted; regardless, the documents would reside in the single table, with additional fields (foreign keys or Type fields) for separation or grouping. Simply, same entities belong together.
2. The documents are already related to each other because they belong to same *business solution file (relational proximity) so they may eventually relate in other ways … Employee purchases Product so becomes Customer; Vendor gets hired as part-time Employee etc (using PEOPLE table as example).  If documents were separate tables, you risk moving the record from one table to the other which is not good practice.  And if the record has children, the move is complicated further (unlikely here but should be mentioned).  Employee might also create document in the wrong table originally, warranting a move later.  The less scripting the better … the more natural a solution runs without intervention, the safer and more dependable.
3. If both House and Non-House documents are created by Employee then separate tables would also require two separate portals to display an Employee's work instead of one.  And in reporting, one could not group by Employee, months or document types because reports are based upon a single (or related) table and you would have two unrelated tables instead.  Each of these needs would add unnecessary complexity if two tables were used.

It is similar to Notes within a solution ... Contacts may have notes and so might Invoices or Products but instead of having a different table for each, we use a single Notes table which cuts down on table clutter (it eliminated 14 needless Notes tables in one solution recently), allows single-table searching, reporting and portal displays and with all Notes (same entity) in single table just add a new foreign key or category field and you have all the flexibility you might need as the business needs change.

 

It is always easier to add a field for a new foreign key or Category, allowing further splitting/grouping or relational filtering of data from a single entity table rather than search two tables, provide two portals, risk a break when moving data from one table to the other, generating two reports, having additional table clutter and potentially deciding to switch to single table later which then is more complex to merge the tables and their children.

... 

Now, having said all that, I always learn from you and my perspective usually shifts to some degree so I would be interested to see why you ask these questions?  My belief that entities should reside in same table is pretty strong so if there are considerations I am missing, I will change.   

 

*All documents are related since they reside in same solution file.  If some documents are business and some are personal recipes (for example) then I would assume they wouldn't be in same file much less the same table.  

[comment]

Yes, objects that belong to the same entity should be in the same table. However, you and Wim seem to think that any two stacks of documents are a single entity just because both contain "documents" (and, you add, because they reside in proximity).

That is where we disagree, because to me it's not enough. It's not enough that objects are alike (and after all, ALL objects are alike at some level of abstraction). To belong to the same entity, objects must not only be alike - they must also be treated alike by the business rules.

For example, in an imaginary precinct solution, we would not put cops and perps in the same table, despite both being "people". We do not collect mugshots of cops, and we don't send birthday cards to perps. And if ever a cop crosses the line, it will mean a new entry in the perps table - not a status change in a People table.

In practical terms, we often tend to push slightly different groups into the same table (as in your People example). Strictly speaking, though, there should be a sub-table for the extra fields unique to each sub-type. This is rather awkward to implement in Filemaker, so we mostly prefer a single table with some fields being empty for each type. Consider, however, a solution with two types of large, embedded documents: one type changes frequently and requires a daily backup; the other type is mostly static and only needs to be backed up monthly.

[LaRetta]

To belong to the same entity, objects must not only be alike - they must also be treated alike by the business rules.

For example, in an imaginary precinct solution, we would not put cops and perps in the same table, despite both being "people". We do not collect mugshots of cops, and we don't send birthday cards to perps. And if ever a cop crosses the line, it will mean a new entry in the perps table - not a status change in a People table.

 

I would put them into one People table.     Both cop and perp have FirstName, LastName, Birthdate, Address, Phone, Email etc.

 

If we are thrown a name we may not know the person's purpose within a solution.  Envision the receptionist at the Precinct getting a call for Jim Smith.  If all people related in proximity (within the FM solution) are within a single table, they are easily found (whether cop or perp or priest on call or building superintendent) simply by looking in the People table.  Additional different fields for Employee, Cop, Perp, Supplier etc would belong in those specific tables.  

 

If a cop crosses the line, his status in the Employee file would switch to Suspended or Terminated but his PEOPLE record would remain untouched. This is 1:1 relationship so it adds no more complexity - the fields reside (and are modified) on either the People or the Employee layout.   In this way, we do not need to duplicate his First Name, Last Name, Birthdate again in a Perp table which opens possibility that in future, one data-set would change if he moved and the other would not.

 

Consider, however, a solution with two types of large, embedded documents: one type changes frequently and requires a daily backup; the other type is mostly static and only needs to be backed up monthly.

 

Then I would still use a single table with the type that changes frequently as a 1:1 related which could be backed up more often independently (probably a separate file). 

 

I have seen too many times where business rules and meanings change and one is then stuck duplicating needless data and worse than that ... duplicate data is dangerous.  Example:  Years ago before computers (I date myself), I was asked to send a check to a client on my rolodex.  It went to the wrong address.  It seems the owner changed the client's address in THEIR rolodex but forgot to change mine.  It would have been better if I had NO address than have the wrong one. 

 

Thank you for explaining.  Your opinion always has an influence and I will add its weight to my considerations and suggestions in future. 

[comment]

Envision the receptionist at the Precinct getting a call for Jim Smith.  If all people related in proximity (within the FM solution) are within a single table, they are easily found (whether cop or perp or priest on call or building superintendent) simply by looking in the People table. 

We do not collect mugshots of cops, and we don't send birthday cards to perps.

 

I guess I should have added "nor do we provide perps with secretarial services"... 

[LaRetta]

It appears that our different stance might come down to whether to design for current business rules (your perspective) or allow expansion/change of the business rules (my perspective).  All of the documents which are 'standard but not assigned' which were mentioned by tobbesfilemaker might (will probably) be assigned a category (or a folder) or by some other association somehow grouped in the future; we humans can't help but do that if the tool is handy and easy to use.

 

When tobbesfilemaker wants to add a table of Customers and begin tracking Customer documents, what should s/he do?  Create a new table of those documents or add a CustomerID field to the existing Documents table and simply begin using it along with its house documents?  Actually if you include the source table (either independently or within the uniqueID) then you can use a single ExternalID but I prefer multiple foreign keys for many reasons.

 

Upon considering 'what is an entity' ... it might best be interpreted to mean 'first word a User would think of when wanting information.'  It is this User perspective which looks for People or Documents because from this higher mountain-top perspective, drill-down is simple whereas jumping from one table to another, finding no answer then moving to the next table is so tedious and unnecessary (in most instances).

 

... I do not think to look for an Excel table or a CSV table or a General Documents table and  I do not think 'find that embedded document which needs to be backed up frequently.'  I think FIND DOCUMENT.  Within the DOCUMENT, I can easily enter part name, type etc. and drill down so from User perspective what entity name would you now give Documents?

 

If I get a call for Joe Smith, I want to look for person Joe Smith and have listed by his name ... PERP/EX-COP so I can tell the caller that he used to have a secretary but doesn't any longer.    I do not want to have to continue searching the BoardOfDirectors table, the BuildingCrew, Volunteers table, the Families table or Staff table.  If I want a report by how many new Precinct associations (new faces in the door) happened over the year, we can give it.  And yes, Michael, I would want on the dashboard ... ALL birthdays listed (inmates, cops, janitors, perps' children even) ... every person in relational proximity.

 

Thank you for the great discussion.  I hope it has been helpful to tobbesfilemaker.

[comment]

our different stance might come down to whether to design for current business rules (your perspective) or allow expansion/change of the business rules (my perspective).

 

No, I think I we are arguing more about the examples than the guiding principles. Especially since you keep rewriting my Jean-Claude Van Damme prison movie into a Susan Sarandon romantic comedy...

I would probably advise the OP to keep a single table, too - once I knew all the relevant facts. All I am saying is that I do not know them yet.

Here at Sea World we have a Staff table: it keeps names, birthdays, children names - even favorite foods. We also keep track of staff work schedule, training, vaccinations, etc. True, some of our staff are human and some are dolphins - but hey, they're all mammals, right?

Now, this is my point: "staff", "mammals", "documents" etc. are just labels. You need to examine the functional significance of a label within the context of the business rules before you can decide if the label represents an entity or not. It is certainly possible to have two or more entirely separate entities, all under the umbrella of "documents" - with absolutely nothing in common between them except that they all require a container field to store them.

[Wim Decorte]

Consider, however, a solution with two types of large, embedded documents: one type changes frequently and requires a daily backup; the other type is mostly static and only needs to be backed up monthly.

 

That's where the Remote Container feature and FM12's new backup mechanism comes into play: with the container data stored externally FM will hard-link all existing PDFs which makes the backup both very fast and conserves disk space.

 

So you get the full benefit of splitting the tables up in a static vs. dynamic table but without any of the downsides.