Hi! We are a small visual artist company. We work on several projects at a time, repeating tasks on different projects, like on animation video production. We don´t know wether we need to create one base with all our projects, with a table for projects, one for clients and one for tasks. Or to create one base for each project, syncronizing the task tables from a generic base. We need to somehow have a general overview in one view from all the tasks due to each project. I hope that you understand what I mean, and that you will be able to help me! thanks!
Whenever possible, you should fight the temptation to create a new base for each project.
It creates too many fractured and siloed locations for data that are fundamentally the same.
Making that mistake is one of the main culprits behind a majority of technical debt that I've seen teams and companies fall into.
That being said, you've actually already arrived at the correct conclusion!
At a high-level, your base should a little bit like this:
Of course, your implementation and field choices will vary to fit your needs and you may have other tables to account for, but this structure allows you to manage a large number of clients, projects, and tasks all within a single source of truth.
Once you have your database design, it becomes a question of how you want to leverage and build your views and Interfaces to cater to the needs of your users and team(s).
By leveraging Interfaces, you'll be able to build a quick dashboard that will show you all the details of a given project and all of its related tasks.
So, we like to think of things in data types.
I could go on for multiple pages about the intricacies of the data types, but for the sake of brevity, you can think of a string as being a sentence of some sort.
Single-line text and long text fields are two that fall under the field types that return string values.
I use the data type name in my Airtable ERDs (Entity Relationship Diagrams) because when I'm writing integrations and scripts, I don't care too much about the field types. Instead, I'm only ever concerned with what type of information a field will return to me when I'm interacting with Airtable via the API.
Additionally, the use of "string" in the ERD gives users some sense of flexibility since they have the ability to define what type of string-based field they want to use.
Some people prefer to use a long text field over a single-line text field. Sometimes it may be beneficial to use a single-line text field over an email field, etc.
In short... in Airtable, a string is a text of some kind.
Great! I think I am almost there. I have two more questions:
1) When in the Clients table you put Projects relationship, do you mean it needs to be: Name of client as a text field, and then Project as a linked field? When I try this: "Sorry, there was a problem saving this field. Can’t save field because it causes a circular reference".
I think I am a bit stuck there.
2)One last question, how do I link Clients with contacts? Should I add another table called contacts and link them?
Thank you so much for your help!