Fundamentals

Only one change point: If you want to add a output column to an API, you should be able to make that change in one place and it works. If you have to make the change in four places, your methodology is crap.
When writing a API connecting a HTML/Javascript client page to the database, it's very fast (2 hours) if you
- Write a stored procedure to fetch or change the database.
- Write a simple controller method to call the stored procedure.
- Use a database wrapper that, given a stored procedure name and parameters as input, run the stored procedure, and return the results back as a JSON string.
- Pass that JSON string back to the web client.
- Every stored procedure should have a test stored procedure written for it.
The truth is: You're not going to reuse it.
- Every time you need to add a new feature to a web page, you first look for an existing web page that does sort of what you want. If you don't find one, you create a new web page.
- This means that every web page is unique.
- The data and API requirements for every web page are unique.
- If you try to reuse some other API from some other code, you'll be trying to make that API do two different things.
- Trying to share API methods between web pages causes confusion and errors when you're trying to juggle between different uses for the same API.
Changes come from either the top down or the bottom up.
- Changes from the top down are requests that the page be changed.
  - When you want to change a web page, it's easy to change the API.
  - That API is only used in one place. If you want to add a column, add it to the stored procedure.
  - If you want to add search capability, feel free to modify that stored procedure. No other web page is using it.
  - If you have something that's used in many places such as a state drop down, create a web control for that and use the control in many places.
- Changes from the bottom up are changes to the database structure.
  - Because all your data access is stored procedures, you can query the system tables to find out which stored procedures use the table and field you're changing.
  - You look at each stored procedure that uses that table/column to see if the change will require a change to the web page.
  - You can often get away with just changing the stored procedure and not changing the web page.
  - You can run all the tests for all the stored procedures at once to test them.
Some web pages have complex demands of multiple recordsets returned.
- You can write one big procedure to return all the data as multiple recordsets.
- Making one big stored procedure to return all the data will run faster.
- You won't need to create a bunch of classes to store all this data. Your database wrapper will convert the multiple recordsets into one big JSON object.
- You could try to read from several existing APIs to get the data so you don't have to write another stored procedure. But if you do this, if you change one of those previous existing APIs, you might break this web page. And you had no idea you broke it.
The name of the stored procedure should not describe what it does. The name should describe where it's used at.
- You're not going to be reusing the stored procedure for multiple pages anyways.
- If you have to change the stored procedure because of a bottom-up design change, you can easily tell what web page you might have to change.
- Naming a stored procedure for what it does encourages someone else to try to reuse your stored procedure. Then when you have to change your stored procedure, you break their web page.

Examples of You're Not Going To Reuse It

Both the admin and the customer facing website show a list of invoices.
- The admin website shows more columns.
- The admin website can show all invoices. The customer website only shows their invoices.
- You're gonna need two stored procedures for this or one stored procedure with a big IF statement in it.
- If you use one stored procedure with a big IF statement, when you want to change one web page, you have to worry about breaking the other web page.
Two different web pages show a drop down list of states/countries.
- You start out thinking they can use the same API. But you later find out that the second web page is only showing states we have warehouses in (because they are different web pages).
Both the customer service rep website and the customer website has a list of products/prices
- The customer service rep can see more columns such as the wholesale price.
- The customer service rep can see products that are not for sale yet or are obsolete.
- Once again, you're gonna need two stored procedures for this or one stored procedure with a big IF statement in it.
- If you use one stored procedure with a big IF statement, when you want to change one web page, you have to worry about breaking the other web page.