Last week I was asked to write a SSRS report against a MySQL database. Never having worked with MySQL, I found this a challenge that I enjoyed. And I got it done.
Today we had a follow-up meeting to learn more about the database, what two applications that run against it and other related things. The data and the applications are related to a lawsuit, so it would be best if I don't go into details. You get the idea that the data is very sensitive. Enough said about that.
What surprised me and what I learned today, is just how long ago the lawsuit was! It started about 30 years ago! I'm not sure if the data has always been in this MySQL database, or if it started its life 30 years ago as an Excel spreadsheet, or a Word document or just paper. And I certainly have no idea how many database types this thing has gone through, over the years, to get to where it is today. Also I'm only just getting acquainted with the database, but already I've found some very strange things. Such as there's a column in a table I used for the SSRS report that's supposed to be a primary key, a string type. With values like 12345. But as time went by, someone wanted to create what would be a self-referential table, but they didn't introduce another column. Instead they used the same column and append things like "A". So 12345A is related to a parent record that has a value of 12345 in the same column. Or, at least, that's how I understand it at this point. I know there can be more, like 12345B. But I don't know if that's related to 12345 or 12345A.
Anyway, that's not the point. The point I'm asking is this, is there any way within SQL Server, or any other types of databases, where you can record the reasoning you make, as a database designer, for doing one thing or another? I don't think there's a lot of documentation on this database. And the data is so important that we just can't "blow it off", if you know what I mean. I think that over time they used Word documents, but they've been lost. Maybe putting documentation into SharePoint? I got to thinking about how to save documentation so someone can see what whoever was thinking 10, 20, 30 years later. After all, this "database" (if it started as a MySQL database) was started when there was no HTTP/HTML, so you couldn't very well have stored it using markdown or anything like that. And even if whoever started this thing, 30 years ago, had written a Word document about what he/she/they were thinking, that's long since been lost. So, it would be great, I think, if you could store your database design thoughts into the database.
Has anyone thought of doing that?