It's All About the Database

by DL Keeshin

---

May 8, 2018

---

---

Having done data architecture and design work for over a quarter of century, the more things change the more they stay the same. To that end, I compiled the following guidelines that have so far withstood the test of time.

1. Automating a bad business process just makes a faster mess. Improve the business process first, then model data.

2. Achieve genuine integration. Have the source system own the data flow.

3. Fix the problem now, not later. Do data cleansing at the source.

4. Complete the loop. If you change data mid-stream or at the target, you'll have to synchronize it with the source.

5. Manage change. Time stamp data with effective start and end dates.

6. Push data, don't pull it.

7. Archive. Archiving simplifies maintenance and improves performance. Build the archiving process first and work backwards.

8. Uniqueness matters. If you have to, use surrogate keys.

9. Automate. Make automated processes scriptable and repeatable.

10. Embrace popular data format standards like XML and JSON.

11. Divide and Conquer. Use smaller more frequent data sets.

12. Normalize for performance, de-normalize for ease of use.

13. In relational systems, narrow tables are faster and more manageable than extra wide ones.

---

Here are some useful categories to links that will help decipher some of the mysteries of SQL Server and other data technologies