I’m designing a database for urban farms. Each farm has specific infrastructure (e.g., greenhouse) that doesn’t vary frequently, and different operations (planting, watering, harvesting) that varies weekly or monthly. If I structure the base as one table with each farm as a separate records, I’ll have to have hundreds of fields. How might I create a linked table that allows me to organize the operational data for each farm on a weekly basis?
Database design question
This topic has been closed for replies.
The usual approach to base design is to model the organization of the system you’re trying to document. For instance, you’re building a base of farms, so your main table would likely be a table of farm records. There are a number of commonalities across farms — for instance, the personnel involved, various instances of common infrastructure (e.g., dual-light greenhouse, hydroponic greenhouse, plot, field, cucumber frame, tiered seedling greenhouse, and so on), probably account information, and the like, each of which would likely be one or more linked tables. There would probably be an inventory of consumable items and an inventory of non-consumable (capital) items for each farm. In any case, you get the idea: If a farm In Real Life contains infrastructure, then in the base the [Farm] record should ‘contain’ — that is, be linked to — records that represent such items.
Abstract attributes are handled similarly, albeit usually with more variations. According to your description, farms ‘contain’ tasks, but there are a number of ways to implement such a relationship. Not knowing much about your exact situation, I could be totally off the mark here, but one possibility might be to create a [Task Instance] table to which each farm would link. Each record in this table would have a formula field for the primary field that would assemble a unique record ID consisting of farm, task type, and date. Within the [Task Instance] record would be such fields as, say, a single-select to identify the type of task; the date the task is due; the responsible party; consumable items required (e.g., seeds, fertilizer); capital equipment required (e.g., tiller, hoe, tractor); task status; completion date; and the like. Many of these might be linked records, in some cases representing links to tables already mentioned. Often, such attributes will link to multiple ‘In Real Life’ records; a task, for instance, could easily be linked to the farm, infrastructure element, responsible party, and fixed and consumable inventories, among others.
My advice would be not to sweat the small stuff: Get a good, rough 60 to 70% on the first pass, enough to make sure the overall structure will work, and start implementing. You’ll find important details make themselves known and unimportant ones fade into the background. Airtable is flexible enough the data model can be radically refined with relatively little pain and suffering; in fact, a full, ground-up redefinition can be less painful than one might expect.
The usual approach to base design is to model the organization of the system you’re trying to document. For instance, you’re building a base of farms, so your main table would likely be a table of farm records. There are a number of commonalities across farms — for instance, the personnel involved, various instances of common infrastructure (e.g., dual-light greenhouse, hydroponic greenhouse, plot, field, cucumber frame, tiered seedling greenhouse, and so on), probably account information, and the like, each of which would likely be one or more linked tables. There would probably be an inventory of consumable items and an inventory of non-consumable (capital) items for each farm. In any case, you get the idea: If a farm In Real Life contains infrastructure, then in the base the [Farm] record should ‘contain’ — that is, be linked to — records that represent such items.
Abstract attributes are handled similarly, albeit usually with more variations. According to your description, farms ‘contain’ tasks, but there are a number of ways to implement such a relationship. Not knowing much about your exact situation, I could be totally off the mark here, but one possibility might be to create a [Task Instance] table to which each farm would link. Each record in this table would have a formula field for the primary field that would assemble a unique record ID consisting of farm, task type, and date. Within the [Task Instance] record would be such fields as, say, a single-select to identify the type of task; the date the task is due; the responsible party; consumable items required (e.g., seeds, fertilizer); capital equipment required (e.g., tiller, hoe, tractor); task status; completion date; and the like. Many of these might be linked records, in some cases representing links to tables already mentioned. Often, such attributes will link to multiple ‘In Real Life’ records; a task, for instance, could easily be linked to the farm, infrastructure element, responsible party, and fixed and consumable inventories, among others.
My advice would be not to sweat the small stuff: Get a good, rough 60 to 70% on the first pass, enough to make sure the overall structure will work, and start implementing. You’ll find important details make themselves known and unimportant ones fade into the background. Airtable is flexible enough the data model can be radically refined with relatively little pain and suffering; in fact, a full, ground-up redefinition can be less painful than one might expect.
Thank you. This is very helpful conceptually. Would you be able to review the database I develop and make additional suggestions on a fee for service basis? This is for a research project so my funds are limited but the database is crucial and we want to design
it well.
Nevin
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.