Is there any way of storing database related information?

Question

Is there any way of storing database related information?

Doctor Who 2

SSCrazy Eights

Points: 8177
More actions
June 1, 2019 at 2:08 am

#3649226

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?
Rod

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Provided that they're not too long, you could use "Extended Properties" (in SQL Server, anyway) to keep track of things. Another method would be to actually store certain documentation in VARBINARY(MAX) columns of a "Documentation" table. That would help ensure that things like "Extended Properties" aren't overlooked in a migration to something else.

Of course, for something like this, there should be an official "WORM" repository somewhere. You brought up "Sharepoint". That would probably be a good idea but it would have to be Write Once/Read Many for sure. The company lawyers should have thought about that long ago. You might want to ask them what would be proper and acceptable.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Doctor Who 2 SSCrazy Eights Points: 8177 More actions · Answer 2

Jeff, I like your idea of using a VARBINARY(MAX). We could do that, if we migrate the data out of the MySQL database into a SQL Server database. At this point I don't know if we will or not. It would mean modifying 2 web apps to point to a different data source. My guess is they probably won't want to do that. I don't know enough about MySQL to know if that supports VARBINARY(MAX). I do know that at least the server the MySQL database is sitting on, is underpowered. When I wrote the proof-of-concept SSRS report, I fetched 5 columns from 1 table. That table has about 55K records in it, which is sorta large, but honestly not that big. The SSRS report timed out before it could produce the report. I had to restrict it to 500 records, before I could produce the report. So, I might have a good argument for at least putting the MySQL database onto a newer server.

I like your idea of talking to the lawyers who were around when this thing came into being back in the 80's. Or at least a lawyer whose familiar with what's going on now, if the other guys have retired or left for better jobs.

Thanks.

Rod

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

Talking with the current company lawyers might be the best thing to do. Of course you'd have to get it in writing but you never know... they might just say "What!!??? We haven't needed that data for years... just drop it".

Don't forget to take a final backup before you drop it if that happens. Put it somewhere safe and long term. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Doctor Who 2 SSCrazy Eights Points: 8177 More actions · Answer 4

About backups of this database, that's a thing. I asked about that, before I started working on the SSRS report. I was surprised to learn that no one I spoke to even knows whether or not it's backed up. It must be, because of the importance of the data. I'll learn more about it the further I get into it.

Rod

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Doctor Who 2 wrote:

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.

Well, GML was first proposed in 1969, long before 30 years ago. And SGML was an ISO standard by 1986, that's 33 years ago, and any accepted version of HTML before the acceptance of HTML5.0 in 2014 was perfectly handled by SGML tools, as all earlier versions of HTML were SGML conformant. So why do you think there was a problem in using a suitable markdown 30 years ago? Surely it could have been done easily in SGML.

This reply was modified 4 years, 9 months ago by TomThomson.

Tom

Doctor Who 2 SSCrazy Eights Points: 8177 More actions · Answer 6

Yes, it could have been. However, from what I've been able to see of the tech they used back in the day, I seriously doubt they had the technical chops to be able to write anything with GML, SGML, HTML, etc. I'm not blaming them. If they didn't know, they didn't know. Perhaps that's the real issue in this case; they just lacked the technical know-how to get a decent amount of persistent documentation in place for future DBAs and developers to work with.

Rod