Could I have an explanation of why we can't hide the primary field?

I grappled with the primary field early on coming from other database tools.

You need to make primary fields meaningful yet brief as they are a visual tool. They are not used in the back end to link actual records. They don’t even have to be unique. I struggled with this initially.

I have found clear primary key field’s are critical to solutions being usable. This is particularly the case with multiple join tables.

If you have a view that shows multiple linked records how are the links going to make sense without a meaningful primary key? What else would you use?

I think my background in relational and indexed DBMS’s may be a stumbling block to understanding this Brave New World.

So I have a Person table that may have multiple people with the same last/first name but I want to choose only one of them when I’m linking to an Organization table. I build my primary field as LastName/FirstName/Uniq where Uniq can get around name collisions.

This pattern is reproduced in lots of my linked tables. I’m also using “link” tables such as in RDBMS - many-to-many. I use these link entries to store more information about the relationship such as when it started/ended, type of interaction, etc.

I’ve also explored and liked the graph DBs such as Neo4J which may be a better match. However I’m also trying to get some semi-technical coworkers to buy into the Airtable approach which seems pretty similar to spreadsheets that they work with all day.

I’m open to a complete rewrite of my test base. It has been extremely useful to me so far but I don’t want to be caught in a bad design and implementation.

Thanks … roger

You have more db experience than me. One thing I am am also conscious of is not creating a join tables unless essential. That is I use select fields if in doubt to try and keep the table count down. The main reason being is that join tables are front end in airtable as opposed to some RDBMS. As such they can be confusing for infrequent base users.

At least airtable is easy to re-build / write. There is just no escaping architecture planning no matter whatever the app platform … :sob:

Hey ripwit

I am gonna try to bridge the gap between databases and airtable, and I am basically just hoping you know SQL. I’m certain you’ve realized the the combination of filters and grouping is analogous to using SELECT/WHERE and GROUP BY functions. So if you were to say like [select * where name = dave] then you’ll get all the daves in your table, and if you say [select * where firstname = dave group by lastname] then you’ll get all the daves, but they’ll be grouped into clusters by last name. Same as putting a filter and grouping on airtable. And that is enough to get someone confused about how this all works. In SQL and many other relational databases, all records have a record id. This is not the case with airtable (at least not in a way that is meaningful at a glance, because RECORD_ID() returns something that I would not call human-readable). If you notice, every time you sort a table view differently, it reorders the record numbers. So row 14 in one view is not necessarily the same record as row 14 in another view. This is important because it means your records do not have unique ids assigned to them that you can reference easily.

I don’t doubt that you noticed that already, but I wanted to point it out to preface the next point. The only way to uniquely identify a record with a single column is by using the primary field. It is also the most visible field, and it is utilized heavily in airtable’s interface. Take the attached screencap for instance. “Dave” is simply not a great identifier for reference, and the whole point of airtable is to show information at a glance. It’s a visual tool. I know you can get great results using it for data analysis, but the point is to be able to deliver that information quickly and in a meaningful way, so you set up the data analysis stuff but the goal should be to deliver information quickly, not just store data. If you want to store data and process it, you should probably be using a database. If you want sequential records for analysis, maybe use [popular spreadsheet program] but what airtable EXCELs at is delivering information visually.

If I were you, I would experiment with the use of empty link columns that are only used to create links which give you access to lookup and rollup fields, and start using formulas for your primary columns which simply create unique identifiers out of multiple columns by doing things like [column2 & " " & column3].

That’s just my thoughts though, let me know if I didn’t quite hit the mark.

Supplementary post here- Just pointing out that using formulas for primary columns in linked tables is absolutely a valid practice

Thanks for your very cogent explanations, Thomas.

Yes, I have a long history with RDBMS and SQL and perhaps that is causing
me some difficulty.

In my designs, I use the many-to-many linking tables similar to the
automatically-generated links in Airtable. However these linking tables
contain a lot of information that doesn’t belong in the linked tables.
Think the Order record that links a Product and a Customer. The Order
record may have multiple instances but the Product and the Customer must be
unique. In the Order record we may record date/time, # of items, etc. So
the Primary Field (to use Airtable’s terminology) would have a value of
Customer&Product keys.

I am adding a lot of Views with filters, groups, and sorts. They work
nicely however I can’t let others try my implementation because it is so
hard to protect design aspects - especially on my trial Pro license.

All in all I’ve been able to build a pretty complex application that is
fitting together well. The additional linking elements in each table are a
bit of a pain but I’ve also found them useful. I don’t know how performant
this would be if there were several 100’s of thousands of records that were
linked in various ways.

i also realize that I can take the design (and partial implementation) that
I’ve put together and realize it on another platform - perhaps a graph or
relational or nosql (whatever that means) platform.

I think I see the problem. In your example, with an Order record that contains links to Product and Customer, you are effectively using the Order record table as a junction table. This is entirely unnecessary and I believe it is complicating things beyond what you would consider an elegant design. In my mind, you have 3 tables.

The Order table’s primary column would be the Order Number, in that case I would have a Product table link that allows multiple records, and a Customer link that does not allow multiple records.

The Product table would have a primary column that is the name of the product, along with an Order link that would allow multiple records (recording every order that involved that product) and it would not need a Customer link unless you desired it for some kind of prediction or analysis to see what different customers or customer types tend to buy. But even if you did that, I would hide that column in the Product table because it would be massive, and instead I would use formula columns to analyze the number of times the product has been ordered for each customer type, or something like that.

The Customer table would have an Order link that would allow multiple records (recording every order that customer has ever made) and maybe a Product link, for the reasons stated in the above paragraph, and utilized similarly.

The important thing here is that the Order table is NOT simply a junction table. To be honest, creating junction tables is kind of difficult in Airtable. It requires a lot of convoluted stuff I don’t like, and you don’t need them because the individual record view feature can make that happen on the fly. If you want to see [select orders from customer where product = chairs] then you can simply drill into the customer record or something. And if you’re drilling into a record and you want to see a related record, you can drill into that from the screen you’re already on. See the attached pictures.

Maybe you should start with what you want to accomplish with your base, rather than what data you have available. In my experience you get more out of airtable when you are trying to answer a question that you’ve already got ready than if you just pour data into it trying to organize it. I’ll post a followup in a second to show you what I mean.

I’m very slow - please forgive me. And if this should be taken to a beginner’s slope (separate conversation), I’ll understand.

Where do you record the quantity of items ordered? And I have multiple “conversations” with the customer and many other details about each order.

Actually, in the real world I have “People” and “Organizations”. Each Person can be linked to multiple organizations and visa-versa. However the role and other values are more attuned to the People <-> Organization link than to the individual person or organization. Think about work email addresses, phone#s, title, projects, inter-person linkages, and inter-organization linkages.

I really like what you are proposing and I’ll do some more serious evaluation about the model. This is a brand new application and not in production so I’m willing to re-implement as it makes sense.

Here is an example base with some items renamed, but the “schema” is still intact. I wanted to have a way to keep track of budgeting for a department, so I made this base. With this setup, you can enter new Line Item records without affecting the budget, and just have the records there for reference. They do not get calculated into the budget until you associate them with a project. When you add them to the project, the assumption is that the quantity specified in the line item for the project is the number you must have in order to have the project be completed once, and then the quantity you use in the Project table multiplies that number again by the number of times you wish to complete that project. It is all summarized in the Departments table where you can see what the asset expansion and operating costs are for each department, and at the bottom of the window, you have a nice summary in the form of a total cost for the entire organization.

In my opinion, the best use of Airtable is to build tools.

Just a side-bar here that you absolutely CAN access the unique record ID that Airtable applies to every record. In a new field of type formula just enter RECORD_ID() and then the column populates with the Airtable ID. Useful if you are feeding data in/out with webhooks too!

Also-- there’s a field type of ‘Auto Number’ which will uniquely number your records. Unfortunately it starts at 1 with no option to adjust that or go with ‘01’ etc but at least you can use this field in a formula to create a Unique record identifier.

This would allow us to have a unique id referenced in relationships and still hide the id field! Thanks for this!!!

Airtable should allow users to hide the primary field. There is no valid reason to prevent this.

I've found a workaround to hide the content of Primary Filed in read-only view for my external clients.
The trick is to create a new base, and sync the table you want to share.
On the synced table in the new base, you'll get the same Primary Field. 
BUT, for some reason, you can change the TYPE of this field. 
By choosing "Date", for example, since me primary field is not a date, the Primary Field will now be EMPTY. 
I just found this trick that now allows me to use shareable view & Interfaces in order to share records to my external clients without sharing the Primary Field (that contains unwanted information for my client)

Hope it could be some people around here!
Pierre-Louis