Rows vs Columns for table data - work orders

[AGairAdmin]

Howdy, 
Work order data: 
when displaying a work order on the "app" 
question at the end 

we have the work orders table laid out with "167" columns, not all of them will contain data, and not all of  them will need to be filled out , so if there is a chem prescription there are 12 groups of columns  totaling 36 columns total available for data entry meaning a total of 12 chemical prescriptions can be added to the order

headers follow this    chem name 1 , rate 1, uom 1 / chem name 2, rate 2, uom 2, etc..... 

Example: chemical prescription info "columns"  / 
Chem name + Rate + unit of measure  = prescription #1 (3 total columns / same row )
Chem name + Rate + unit of measure  = prescription #2 (3 total columns / same row )

so out of the 167 columns 6 will have data in them for that part of the info, 

Complete weather data 
5 columns = 1 weather data 
same layout example as chemical scripts  = 60 columns total for weather data per row = wind 1, wind 2 , wind 3 etc..... 

So now to the question 
is it possible to display to the user, columns that have data already and be able to add data either be a button to "add weather" or "add chemical" to the next "data" column(chem name 1, chem name 2) or to edit the current column  



we think by using 1 row per work order with that many columns will streamline how the data flows, and allows for better database management.

Thanks  

[Joseph_Seddik]

This is definitely not better database management. Each one of these categories should be in its own table, with its own column, while the work orders table would be referenced by those table as needed. You should not use columns for lists as in chem1, chem2, chem3 etc. This is bad data modelling.

Please read this guide and follow the links inside:

Data: The Essentials | AppSheet Help Center  

[AGairAdmin]

can we ask why?

why is tracking all of the order data on one table bad, or what could it lead to.

things not syncing?
other than it being a large spreadsheet with a crap ton of columns. (there are other tables for other data to reference the work orders table)

what is the benefit for split out each category into separate table, 

how would that function in archiving changes to the orders, one of our processes is after an orders has been generated, anything that changes the original order. the OG order is archived into a seperate table and the updated work order gets a "-1,-2,-3" etc to designate the revision number. 

i am not a developer and i have no formal education in code or app development

The last project i did was leading a team of devos (akin to herding cats) to link legacy DBs to api and cloud, and i ran into a lot of, you can't do that, or it doesn't work that way and i pushed them to do it anyway and it turned out to be the best thing since sliced bread, that company now uses those processes in their everyday work. 

[Joseph_Seddik · Accepted solution]

Sure you can! and I will tell you why. I'm sure my friend you are very good at what you do, so I'm interested in sharing a little thought with you, before answering your question. 

Sure you can make anything work and there are often many ways to make something work in any dev environment. A developer however is constantly asking himself, in every step, every line, is this the best way of doing it? What is the cost of running my code? Does my code ensures the most efficient use of memory, battery, processing power and bandwidth? Am I wasting resources unnecessarily? Am I ensuring the fastest load and run times? 

Now to answer your question. There are two considerations.

The first is a general one. When you are approaching a new dev environment/platform, especially when it is closed and you do not or cannot see the underlying mechanisms of this platform, you will always be better served to follow its official recommendations and guidelines. The developers of the platform invested time and effort to write and maintain these guidelines for a good reason. AppSheet has official guidelines telling creators how to model their data. 

More specifically, about databases, why should your data be in rows rather than in columns and why in your case you should create several tables not one? There's a wealth of resources on this very subject online and you should be able to google them easily, but I'll give you a few:

1. Database size:

• With your data in columns you will have a lot of empty cells, since not every work order will need every column in every chem and every weather and every other possible category in your table. That's why you want to hide them. 
• Don't think just about storage space where files will be compressed anyway, but every empty column en each row will consume a bit of your scarcer memory space. AppSheet official guides even advise you to remove empty spaces surrounding your data in the spreadsheet. 
• With the rows approach you have only as many columns and as many rows as needed. 

2. Flexible and consistent data structure: 

• What if at one point you need four or more chems not 3? What if you need additional columns in one of your category? what if you need a new category? You'll face the need to change the structure and all its dependencies. 
• On the other hand, you can for example easily add as many additional chems or any other additional instances in category as needed, without a single change to your data structure. 

3. Use of simpler and more efficient expressions:

• For decades databases have been designed to have data in rows, not columns, and the evolution of database expressions followed suite. Yes you'll still be able to extract data from columns but you'll find yourself writing longer more complex and less efficient expressions, simply because the available expressions are designed to deal with and manipulate data in rows. AppSheet is no exception. 
• It is not only data manipulation, even simple operations like showing data. You want to hide null data from users? Sure it is possible, you just to have a write the corresponding expression inside each and every one of your 167 columns. On the other hand, with data in rows, these empty rows simply do not exist. 

[AGairAdmin]

Thank you, that was a very helpful explanation to understand why it should be organized into smaller tables, 

how far should the database be split down 

lets use weather data 

wind, temp, humidity 

would a table called "weather" then have 3 columns  //or// have a table called "wind" another called "temp" etc... 

[Joseph_Seddik]

That's a very good question !

How many tables should you have and how far should go splitting your data into different tables? I'll come back later this evening with my take in this. Just wanted to tell you now that your question is really good one!