Before I start filling out it, I thought it would be good to ask some people’s s opinions to know if I am following the correct path or if I should try another way.
Imagine I have a database for a farm, I would like to try to organize the tasks that are performed there, including the inputs/products used to perform each task.
Which one is the best path to follow and track in the future? (Feel free to suggest an alternative.)
First option:
First table: Tasks (with linked record to the second table).
Second table: Inputs / Products.
(Every time I use a product I create a new record with its respective quantity, the unit of measure, price, purchase date, etc. even if an identical product has already been used).
Second option:
First table: Tasks (with linked record to the third table).
Second table: Catalog of Inputs / Products (category, type, length, weight etc).
Third table: Inputs / Products used (with linked record to the second table specifying the quantity of the product used).
(I don’t know if the second option makes sense at all I just thought it could be a way to make data more clear and in the long term have a catalog of products so I have to fill less information, for example, If the product is already in the catalog, I just have to link it and fill the price it was paid, the purchase date and attach the files relate to it - warranty etc.).
I voted for option 2 because it sounds cleaner in terms of relational database design (which dictates that you should put different entities in different tables).
So here is the caveat: It sounds like you’d have a 1:1 relationship between table 2 and 3. In this case, you’d probably not really have three entities, but two. Voilà, in this case Option 1 would be preferrable.
Lastly and 1000% generally speaking: In some cases it may make sense to not follow design guidelines to the letter, if technical cleanliness comes at the expense of inefficiency/overhead.
What’s been bugging me is that every time I add a new record I have to categorize it in multiple columns, whereas in the second option I would just link a product from the catalog and fill fewer columns.
As for the views, thanks for the reminder, even though I’m not an expert I’ve been trying to get the most out of them!
Feel free to suggest any alternatives or any improvements I can make!
As I said to @Karlheinz, what’s been bugging me is that every time I add a new record I have to categorize it in multiple columns, whereas in the second option I would just link a product from the catalog and fill fewer columns.
I am far from being an expert on databases and their relationships, so if I make any mistakes, please feel free to correct me and point me in the right direction to improve my knowledge.
About the relations between them, in the second option, wouldn’t the relations between them be direct between the tasks, products bought, and products in the catalog? Or not due to the fact that products will not always be used in the tasks?
I am trying to adjust the flow to the greatest efficiency and operability, in which case I want to choose the path that is most feasible and that in the future I can see/track costs, processes, etc.
Whichever option you think is most feasible I will believe, since I don’t have that much experience and may end up having to spend a lot of time in the future readjusting.
Would you still stick with the second option?
Feel free to suggest any alternatives or any improvements I can make!
I’m sorry If It seems rude to ask it again, but, If possible, please, as soon as you have a spare time, would you mind giving your final thoughts about my other reply?
I don’t know enough about your use case. I am not sure I understand your data structure. Your last post did not really shed light on it.
You would help me (and maybe others), if you explained in greater detail your data structure. Additionally, the data structure should also be informed by the kind of reports you wish to create. All of that determines database design.
Do it like thousands of other people who have used SeaTable to develop powerful processes and get their ideas and tasks done more efficiently.
