Help

Re: One table or different tables - best practice

Solved
Jump to Solution
2490 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Michael_Lever
7 - App Architect
7 - App Architect

Following the advice to have all info on one table and use views, I have successfully created a one table base for my law library. For views separating case law and articles, for example, is easy. I have a field in which I enter a letter according to the subject. eg for cases, enter C, for articles enter A.

For a different project, I should also like to have a one table base, but am unable to work out how best to add records where each primary field record (the record in the primary field) and its different fields have numerous records in the same field.

For example:

Existing Setup:

2 tables. Table 1 and Table 2
In each table I have 3 fields including the primary field.

In Table 1, this primary field is renamed Property
Next field is Y, the next field is Z.

In Table 2, the primary field is Date.
Next field is Location, next field is Notes.
Location field is linked to Y in Table 1.

Add 5 records:

In Table I in Y
enter Lindon
enter Birmingham

In Table 2
Record 1
Primary field Date I enter date.
in field Location, I link to London (in table 1, Y)
In field Notes, I enter a note.

Record 2
Primary field Date I enter date.
in field Location, I link to Birmingham (in table 1, Y)
In field Notes, I enter a note.

Record 3
Primary field Date I enter date.
in field Location, I link to London (in table 1, Y)
In field Notes, I enter a note.

Record 4
Primary field Date I enter date.
in field Location, I link to London (in table 1, Y)
In field Notes, I enter a note.

Record 5
Primary field Date I enter date.
in field Location, I link to Birmingham (in table 1, Y)
In field Notes, I enter a note.

The two tables are now populated with the example.

Group to field Location and sorption date, I now see 3 records for London each with date and notes alongside; and 2 records for Birmingham (ditto).

Issue
I seems to me that two tables makes sense: I can group to Location and have all the entries for each record listed in date order with their notes alongside, AT advice is to have one table and use different views. So how would I achieve what having 2 tables would do?

The only thing I can think of is for Table 1 to contain (in addition to any other fields) two further fields, one for date, one for notes and for a view filtered accordingly.

Surely that would result in a lot of clutter in Table 1? If so then would that matter?

Or this is a case where having tables makes more sense?


In the same base, I have a table for contacts.
In the primary field I put the contact company or person.
In subsequent fields, tel, mobile, email, address, role, link to Table 1 field Location, etc.

If I were to put all the contact into Table 1 then the primary field in Table 1 (X) would contain the unique stuff regardless of topic, with numerous fields for everything else. Again would that matter?

tia

1 Solution

Accepted Solutions

I think you might be misunderstanding the advice you were given. The advice to use a single table with multiple views isn’t meant to apply in every situation. There are plenty of cases where multiple tables make sense. It all depends on the data you’re tracking.

The problem that some users run into is when they put the same data on multiple tables with different secondary info on each table. For example, say someone is tracking students in a class. They may be tempted to put the same student records into multiple tables, using the justification that one table is for basic student info (email, phone, etc.), and the other is for storing grades. That would be a case where using a single table is more efficient. Each student record only appears once, and you use views with different field combinations to show core info in one view, and grades in another view. In fact, that’s how I organize a class I’m teaching. I have about a dozen views in my [Students] table, with each one showing different combinations of the 80 fields on that table: basic info, grading for various weeks, extra credit notes, final grades, etc. It doesn’t make sense to split that across multiple tables, because it’s all related to the same students.

However, if you’re tracking contacts, locations, call logs, appointments, etc., those should each have their own table because they are unique data items. Back to my class example, I would never mix my course info in the same table as my student info because it’s different information.

Does that clear things up?

See Solution in Thread

6 Replies 6
M_k
11 - Venus
11 - Venus

Hi @Michael_Lever

Usually, when there is more then one table, the data is entered into table 1, otherwise, it gets too confusing. Which it sounds like you realizing this.

So, with that in mind, perhaps you can setup your table with a focus of entering data in table 1.

Hope this might point you in the right direction.

Mary Kay

I think AT’s ‘advice’ to have one table and numerous views is confusing. Many of the case studies on AT feature multiple tables. I am going to stick with my setup.

My example might come over as confusing but in practice a doddle: I just wanted to know whether since AT recommend everything on one table and different views how to enter hundreds of notes for the same primary field without having hundreds of rows for the same primary field on the one table.

I think you might be misunderstanding the advice you were given. The advice to use a single table with multiple views isn’t meant to apply in every situation. There are plenty of cases where multiple tables make sense. It all depends on the data you’re tracking.

The problem that some users run into is when they put the same data on multiple tables with different secondary info on each table. For example, say someone is tracking students in a class. They may be tempted to put the same student records into multiple tables, using the justification that one table is for basic student info (email, phone, etc.), and the other is for storing grades. That would be a case where using a single table is more efficient. Each student record only appears once, and you use views with different field combinations to show core info in one view, and grades in another view. In fact, that’s how I organize a class I’m teaching. I have about a dozen views in my [Students] table, with each one showing different combinations of the 80 fields on that table: basic info, grading for various weeks, extra credit notes, final grades, etc. It doesn’t make sense to split that across multiple tables, because it’s all related to the same students.

However, if you’re tracking contacts, locations, call logs, appointments, etc., those should each have their own table because they are unique data items. Back to my class example, I would never mix my course info in the same table as my student info because it’s different information.

Does that clear things up?

Thank you, yes very helpful.

Currently I am using FileMaker Pro 15 (having started with FM12) but as a flat-file database. On AT Pro, I have a base for my law library with one table and numerous views, along the same lines as my FM database but more flexible: for example, Group is superb for spotting duplicates by using two keywords for each record, one is for the first letter of the case, the second for the first two letters of the case: for example, A AB. A. AC. etc. That way Group splits up all cases beginning with the letter A into sub-groups for quick finding. By the time I’ve finished adding records to this base, I would estimate circa 10,000 records, plus a steady stream of new records weekly. [On AT Pro, I dowloaded a couple of the law templates to see how they differ from my approach but too fiddly for my needs, albeit a definite improvement imo on the rather cumbersome Clio Law which I am also using but whose sub I shall not be renewing as I have found it slows my productivity.)

I also plan to use AT Pro for my Shops database. Here I am talking big numbers, circa 100,000 records to begin with, adding one by one will be fun: yes I know I could export and import but that would simply pile error on error and I’d still have to check: much better in my view to use the opportunity for a complete overhaul. As you rightly imply, to track contacts, locations, call logs, etc on one table wouldn’t make sense.

I’ve been looking for something like AT for years and having found AT I am delighted. The snag with ‘Google’ I have found is that when I use search terms that I think describe what I’m looking for, invariably a different sort of app has commandeered them.

Glad that I could help! A couple quick notes about your follow-up comments:

Airtable is just Airtable. There’s no Airtable Pro. The part of Airtable that can be Free, Plus, Pro, or Enterprise is the plan for an individual workspace. In your case, you’ve got one or more bases in a Pro workspace.

Just a heads-up, the Pro workspace plan only allows up to 50K records. If you expect that your Shops base will be larger than that, you’ll likely need to talk to someone at Airtable about the Enterprise plan.

“…the Pro workspace plan only allows up to 50K records.If you expect that your Shops base will be larger than that, you’ll likely need to talk to someone at Airtable about the Enterprise plan.”

I know that, thank you and have already made contact.