Relational database setup help

[stephanie_crock]

Newbie here.
I have two tables I am trying to link…

table #1. which has basic plant info…name, details, photo. this is the master list of all the plants I’m interested in growing.

table #2. these are all the plants that I have seeds for.

I made the second table and was able to grab the plant name from table #1, but now I’d love to include a photo of this plant from from table #1. I tried making a new field on table #2 with the same name, and nothing happened.

question: do I have to manually link all the photos for every plant on table number 2?

[WilliamPorter]

Welcome to the Airtable community, Stephanie.

So you have a parent table (the generic PLANTS table) and a child table (MY SEEDS, the plants for which you have seeds). To show a pic of the plant from the parent table (PLANTS), after defining the link to related record and linking a seed to a plant, you would use a second column defined as a “lookup” column.

To link MY SEEDS to PLANTS, start in MY SEEDS (usually good to start in the child table), create a new column and select type “Link to another record.” Select PLANTS, and you’re done.

Now to get the picture, in MY SEEDS, create another column, select type Lookup, and configure the lookup to pull a value from the PLANTS table, Picture column.

Here’s an example using canine breeds and a couple of my own dogs. Notice that one dog, Lulu, is linked to two breeds (Cairn Terrier and Border Terrier) so she has two pictures. Harry on the other hand is linked only to Blue Heeler and that’s the only pic that appears here. The pics are stored in the BREEDS table.

I do have a question for you, though. Do you really need to make this relational? Maybe you do. Maybe it makes sense. But if the seeds are all one-species of plant, then this isn’t a one-to-many (PLANTS to MY SEEDS) relationship, it’s a one-to-one. Do you perhaps have multiple, um, packets of seeds for the same plant, perhaps from different seed sellers? In that case, it would make sense. Otherwise, you could just have a list of plants and something like a check box column to indicate which ones you personally have in your collection.

Makes sense?

William

[WilliamPorter · Accepted solution]

stephanie_crocker:

question: do I have to manually link all the photos for every plant on table number 2?

One addendum to my earlier responses. If you set up the lookup column for the pictures, then the answer to your question quoted above is NO: You won’t have to manually link individual photos. See the screenshot of my example. When I created the record for Lulu and linked her to the BREEDS records for Cairn Terrier and Border Terrier, the photos for those two breed records were automatically displayed in Lulu’s row of the MY DOGS table.

William

[stephanie_crock]

it worked. thank you.

I did think about having this in all one table; however, I plan on creating a seed inventory each year so having them separate seems to prevent having one MEGA table, and my plant info table already has so much information it’s really hard to input new plants.

I do however, use different views in my MEGA table to isolate information based on the task I am doing: planting, harvesting, figuring out which plants have like needs, etc.

It’s one of those projects that I’m also using as a learning tool. But thanks for your help and Harry is a cute puppy! I had a Blue heeler mix and she was a fun and feisty dog!

[WilliamPorter]

Great to hear that it worked, Stephanie.

Your explanation of why you separated the tables makes sense. Relational design or “modeling the data” is often one of the trickiest part of setting up a new database (or “base” in Airtable’s terms), because there are typically many potentially right answers. The technical limits of your platform are among the things to consider. If your MY SEEDS table is going to have a lot of records in it, over time, then perhaps duplicating the table each year makes good sense. Tricky question though: How many is “a lot” for Airtable? In the platforms I know best (FileMaker, 4D and some others) a million records in a table is not a big deal. But Airtable clearly prefers to keep tables smaller.

Thanks for the comment on Harry. Yes, he’s feisty. But that wasn’t a picture of Harry – that was a pic of a Blue Heeler I found through DuckDuckGo. Here is a pic of Harry himself.

The adoption agency says his mother was only 8 lbs and clearly part schnauzer. Harry’s already 9 lbs, perhaps 10 lbs as of today. And we don’t see any schnauzer. We hope he doesn’t get too much bigger than Lulu, who’s 19 lbs. But he’s a lovely pup.

Good luck with your project.

William