July 4, 2017 at 1:34 am
"There seem to be so few ER diagrams in the real world, especially from vendors, who should always produce one for clients."
Taking this further, I have seen very few vendor supplied databases that had even the basics of a competent database design.
For example, I examined a vendor database with over 1000 tables that had:
No primary keys, unique constraints, or unique indexes
Very few indexes
No foreign keys
No column defaults or column constraints
All columns nullable
I have given a basic database design problem in interviews (3 related tables). I assume that anyone who cannot answer that is probably not going to be able to do good database design work or create an ERD.
July 4, 2017 at 9:53 am
Years ago (much earlier in my career!) I was a "Programmer/Analyst" writing COBOL applications to support a large batch and interactive system we had at a local Ontario community college. The underlying database structure was a "Network" database (SEED) and everything was "Set" based. I did not have a lot to do with the actual database in terms of making physical design changes etc... but as a developer what I found most useful was having a large printed database ER diagram hanging up on the wall. Each developer had this and we also had one in our meeting room. I found it very useful when brainstorming a new applications design or determining how and where some new required data components may fit into the existing DB schema. This was really the only place I've worked that had a larger printed ER diagram (or an ER diagram at all!). I am currently a DBA/Developer at the local school board and one of my jobs is maintaining the data warehouse - which is huge. There have been many, many times where having a printed ER diagram hanging in my cubicle somewhere would have come in so handy! I have often said to myself that this is something that I really need to do but it seems that the day to day tasks and other project work that comes along gets in the way of ever actually doing it. It is something on my to-do list however and after reading this article, maybe I will start chinking away at it here and there. I always found it so much easier to be able to visualize how I was going to access the bits of data required for a query/report or what have you when the entire db was hanging up in front of me. They say a picture is worth a thousand words and that sure held true for me!!
July 5, 2017 at 2:42 am
"There seem to be so few ER diagrams in the real world, especially from vendors, who should always produce one for clients."
I'm working on a database migration now between two systems that have in the region of 1000 tables. Unfortunately neither the receiving or original systems have ER diagrams. I am working out the structure from the UIs as I am able to identify the table and field in the UI. It is slow however and I have been writing SQL for three months now. Interestingly the Vendor DBA who is receiving the information doesn't have an ER diagram either (despite being the DBA) He is a competent individual. The product has been developed over 30 years - luckily one of the consultants was around when some of the modules were being developed and they have been able to point us in the direction of some obscure junction tables. Similarly we have a user here who is good with Crystal and he's worked out most of the relationships in the old system. The good news is that most of the modules are reasonably normalised.
My first question of a new product is usually can I see a basic ER diagram. I've only been involved in the purchase of a couple of systems and no one has given me a diagram. It seems to get washed under the carpet.
I just accept this but choose to make ER diagrams for the systems that I am involved in - They are far from complete I just list accurate table names with accurate key and foreign key field names of the really meaty tables. Don't bother with lookups unless they are very commonly required.
I wonder sometimes whether companies consider ER diagrams as IP ( which to me is a bit like a mechanic trying to patent a circle )
cloudydatablog.net
July 5, 2017 at 6:16 am
Michael Valentine Jones - Tuesday, July 4, 2017 1:34 AM"There seem to be so few ER diagrams in the real world, especially from vendors, who should always produce one for clients."Taking this further, I have seen very few vendor supplied databases that had even the basics of a competent database design.
For example, I examined a vendor database with over 1000 tables that had:
No primary keys, unique constraints, or unique indexes
Very few indexes
No foreign keys
No column defaults or column constraints
All columns nullable
Sadly, I've seen something similar. At a previous employer (who was an Oracle shop) we had a proxy server that logged internet activity to a SQL Server. The design was much like you described above. The sad thing was that the GUI interface to query internet history for an employee wouldn't work. The company "didn't know what to say" about it when they were asked to support it. Since I was the only one who knew anything about SQL Server, they had me take a look at it. A few well-placed foreign keys and nonclustered indexes make a huge difference, even to a database with such a poor design.
July 5, 2017 at 6:51 am
For most interviews I've conducted, we never get to design questions because they don't even know how to get the bloody current date and time.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2017 at 7:31 am
Jeff Moden - Wednesday, July 5, 2017 6:51 AMFor most interviews I've conducted, we never get to design questions because they don't even know how to get the bloody current date and time.
I don't know where you get your candidates, but where we get ours, they at least know how to get the date/time in SQL Server and then beyond that a lot more. Maybe mention to your HR director how much IT and executive level time is wasted by going through an interview process for an unqualified candidate.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply