November 15, 2007 at 5:46 am
Hi Ian,
Thanks for the info on the reference books.
I went across the ERWin course too and noticed that it seemed mostly on the tool, not the concepts.
I might be looking at a university course next year, as I am not lucky in finding a good course.
Thanks again.
November 15, 2007 at 7:28 am
Thanks Ian, that information was very useful. I've got a question for you. Do you have any recomendations for a data dictionary tool? How to you keep up with allowable values for a specific column that does not have a reference table?
Doug
November 15, 2007 at 6:18 pm
Do you have any recomendations for a data dictionary tool? How to you keep up with allowable values for a specific column that does not have a reference table?
Well, any half decent data modeling tool should include the necessary data dictionary functionality. I have no experience working with some of the standalone tools that are available. We currently use PowerDesigner DataArchitect as our main tool. It supports linking to files such as spreadsheets, which is where we store our business name to physical name abbreviations. We have configured DataArchitect to automatically pickup and convert the business name to the format we want to implement physically. For example, if the business name of the data element is Customer First Name, it will look up these words in the spreadsheet and create the physical name of CST_FRST_NAM. The tool supports defining domains and then assigning domains to attributes. Within these domain definitions you can specify the list of aceptable values. This is where the information captured from the business is stored. Physically, we make it a practice to ALWAYS create and populate reference tables and we make sure that the proper referential integrity is defined. Whether or not the application developers decide to use them for look up is up to them, by by creating the tables and enforcing the RI, we in database land are ensuring that bad data is not inserted into the database. Due to the complexity of the applications and the fact that we support more than just a english, it is very rare that values are hardcoded in. We have also learned the hard way! An example of this was a recent change to a sales tax. When the tax was introduced in the 80's, it was set at 7% and never changed, until last year when it was dropped to 6%. Some developers, taking the easy way out, had hardcoded the value. Needless to say, after having to open up the source code for every app that calculated the tax and modifying it, they now look the value up in a table. Now, if performance becomes an issue, local caching can be employed. We have one widely distributed app (i.e. used over WAN, sometimes via dialup) that uses a desktop DBMS to cache the reference data. A table in the coporate database stores last update times for the reference tables and when the remote app starts up, it checks to see if there is newer data and if so, downloads it into its local database. This alleviates the problem of calling across the wire to retrieve the infrequently updated data. In another shop I worked in, we did use the idea of resource dlls, but it solution was implemented as both CICS shared data tables and assembler modules. (it was on a mainframe with 40,000+ online users) In this case, the tables were reloaded and the modules recompiled using the data from the database. The same ideas could easily be translated to COM+ objects and COM dlls or .NET assemblies.
The key is to have a well maintained dictionary and to enforce its rules as much as possible in the database, and I would really discourage the hard coding of values in apps. Too many times the business has said something doesn't change and then, two years later, they need a new value.
If the budget isn't there for purchasing one of the higher end modeling tools, you can use a spreadsheet or even the DBMS itself. By defining check constraints on columns you can store and enforce the domain of values allowed. SQL Server, and many other DBMSs support the addition of extended properties to columns. These properties can be used to store business names and descriptions. Just make sure that what is captured is maintained. If it isn't, you might as well toss the entire thing out. If there is no confidence in the correctness of the dictionary it won't be used.
Well, not sure if that was much help, I have had the luxury of having access to high end modeling tools for much of the last 10+ years so haven't had to go looking for alternatives. Also, if there was a bit of a rant going on about developers, I apologize. I just got home from work. 🙂
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.
November 16, 2007 at 7:19 am
No problem Ian, I wear the Developer and DBA hat and have used many of the same techniques, including my own home grown tool. We're in a relatively small IT shop and I was curious what other people were using.
Thanks for your reply
November 16, 2007 at 8:57 am
I have Sql Server 2000 Database Design by Louis Davidson, which is outstanding. He's upgraded it with several other authors:
Pro SQL Server 2005 Database Design and Optimization
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply