January 18, 2011 at 8:08 am
Ken - I think you missed the "An Introduction". You bring up some points that would be a good follow on article. Why don't you write up an article that follows on to Paul's but adds some more advanced concepts and continues to explain them in a simple way. I'd love to see a part two.
__________________________________________________
Mike Walsh
SQL Server DBA
Blog - www.straightpathsql.com/blog |Twitter
January 18, 2011 at 8:13 am
Paul:
Great primer. Showed it to some of my staff and have been able to raise the curiosity level so we can move away from spreadsheets. Once again, great article.
Jim
January 18, 2011 at 8:33 am
re. Inroduction. Well I suppose it depends on what you feel are the "fundamentals" of our work in DB design. Personally, concepts such as natural business keys (the author jumps right into physical issues ala surrogate keys and misses this concept completely), historical record keeping concepts, etc. ARE the fundamentals, from my experience as a trainer.
I just think this is a primer based on someone's mixing up physical with conceptual and logical DB issues- a serious flaw that is the bane of our industry in my opinion...
ken
January 18, 2011 at 8:45 am
kenambrose (1/18/2011)
I believe there are some serious flaws in this article. For example, recepts represent a historical record of a transaction occuring in a moment in time.If one normalizes the customer as is done in this example, and simply uses a foreign key in each receipt, what happens if a customer changes their address at some later time? or name? (people do get married).
This isn't an instruction manual to type 2 SCDs, I'd consider this a typical approach to an OLTP system. The whole purpose of a database like this is that if a customer's data changes that we minimize updates to correct data.
If Paul were writing an article on the basics of designing a data warehouse, then he could cover handling the type 2 SCD here in the ETL process, though I think this would be over Bob's head.
But I've seen too many multi-million dollar ERP databases handle this sort of customer record as "present value" exactly this way to completely discount it.
And if the customer data has changed enough, a new customer record could be created with a foreign key chaining in the same table to point the old record to the new.
January 18, 2011 at 8:49 am
re. new customer record. And there is no unique index on the business key. So as a result, an unlimited number of exact duplicate customer records could also be created from the design.
I don't know about you but that is exactly the type of nonsense I see repeatedly in the field when people who do not understand the basic concepts of DB design attempt it.
My statement stands. Bob's orignal paper record keeping is more accurate than the DB design presented...
January 18, 2011 at 8:55 am
kenambrose (1/18/2011)
re. Inroduction. Well I suppose it depends on what you feel are the "fundamentals" of our work in DB design. Personally, concepts such as natural business keys (the author jumps right into physical issues ala surrogate keys and misses this concept completely), historical record keeping concepts, etc. ARE the fundamentals, from my experience as a trainer.I just think this is a primer based on someone's mixing up physical with conceptual and logical DB issues- a serious flaw that is the bane of our industry in my opinion...
ken
That's the great thing in this industry and community - we can have differing opinions and get along just fine. I saw this as a missing element when discussing concepts of normalization and design with clients, not a primer on data modeling, the difference between a logical and physical model, dealing with data that changes in time, etc. I saw it as a getting the basics out that are often missing when talking to the business. I also can think of plenty of banes of our industry but not sure I agree 100% that the mixing of physical and logical design phases is necessarily one of them.
At any rate, I think you should write a follow up article to help explain the concepts you feel are lacking in this article and write your article with the same simplicity. Perhaps you will help folks out who are struggling with the same points you find yourself struggling on.
__________________________________________________
Mike Walsh
SQL Server DBA
Blog - www.straightpathsql.com/blog |Twitter
January 18, 2011 at 8:59 am
I think that the receipt issue could be a flaw in this system, depending on the way the business is defined. Perhaps Bob always wants the up to date information for a receipt. Not likely, but possibly.
Bob's system is not necessarily better, just different. It requires a lot of work that could be avoided.
This article does a good job of helping understand the benefits of normalization and why you design databases to do certain things it evolves over time, and perhaps the next evolution separates out the receipt addresses. To trash this article and say it doesn't provide good information is a fairly poor commentary. You focus on a flaw and ignore that there are other benefits to explaining database design to people.
You CANNOT explain everything at once to someone as they learn about good design habits, RI, and other concepts. You have to evolve them along the way as they learn pieces and can begin to understand more advanced concepts.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 18, 2011 at 9:06 am
Paul, The approach you took in this article is marvelous. Top notch job on that.
This article is a good start for people who has never designed a database before.
-Roy
January 18, 2011 at 9:11 am
An excellent description for the layman. I like the way you tell a story to show how databases differ from other methods people are more used to using. It also can help database people understand how business people get into the situations they do. It gives the reader the basic ideas and should be enough to let the layman think "Aha! So there are ways to use this where it'll work better." And then either read more or find an expert.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
January 18, 2011 at 9:15 am
<<struggling with the same points you find yourself struggling on>>
Happily, I haven't struggled with the issues I have just described since many years 🙂 Thanks to Fabian Pascal for that. Check out his "Fundamentals of relational database design" (around only 100 pages with many large graphics) or any of his papers at dbdebunk dot com
ken
January 18, 2011 at 9:17 am
kenambrose (1/18/2011)
re. Inroduction. Well I suppose it depends on what you feel are the "fundamentals" of our work in DB design. Personally, concepts such as natural business keys (the author jumps right into physical issues ala surrogate keys and misses this concept completely), historical record keeping concepts, etc. ARE the fundamentals, from my experience as a trainer.I just think this is a primer based on someone's mixing up physical with conceptual and logical DB issues- a serious flaw that is the bane of our industry in my opinion...
ken
Go to most business people and start explaining databases with discussions of natural vs surrogate keys and watch how fast their eyes glaze over. I've seen people try to talk to business people as though they're training dbas and after the training session is over, they all go back to their computers and keep using their spreadsheets.
This article is great because it describes how they can benefit from upgrading to databases without drowning them in theory or technical talk.
And not everyone thinks "natural" keys are a good solution, that's hardly the first thing I'd bring up in training laypeople. There are many concepts they need before you start talking about that.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
January 18, 2011 at 9:20 am
I believe you have to take this article in the way in which it was written.
In explaining the first steps of data normalization to a novice it excels.
In describing the *best* way to implement a customer/sales database it has flaws, unless you make certain assumptions.
In Bob's case it may be safe to assume that customers are not going to change address during the lifetime of his company.
Similarly, he might never change his prices over time (similar problem with changes in customer address).
I look forward to Part 2.
Kelsey Thornton
MBCS CITP
January 18, 2011 at 9:20 am
kenambrose (1/18/2011)
<<struggling with the same points you find yourself struggling on>>Happily, I haven't struggled with the issues I have just described since many years 🙂 Thanks to Fabian Pascal for that. Check out his "Fundamentals of relational database design" (around only 100 pages with many large graphics) or any of his papers at dbdebunk dot com
ken
Appreciate the sarcasm. I wasn't implying that you were struggling on those points but rather meant to say "struggling with their lack of inclusion". At any rate, I am looking forward to reading your article, if you choose to write one. I am sure the combination of your love for relational database design, your strong feelings on what is missing in the industry in a lot of projects and your experience as a trainer you'll have a great article as well. Looking forward to reading it someday. Have a great day.
__________________________________________________
Mike Walsh
SQL Server DBA
Blog - www.straightpathsql.com/blog |Twitter
January 18, 2011 at 9:23 am
<<To trash this article and say it doesn't provide good information is a fairly poor commentary. >>
I respectfully disagree. To provide a teaching example for a database design that allows- ney almost guarantees - that if it was actually used it would result in:
1: numerous duplicate customer records
2: inccurate reports of data in receipts
3: defines only surrogate keys which directly contributes to 1 and 2 above.
is simply not a good instructional example for beginners in my opinion. Especially since those two flaws are *exactly* the type of nonsense we often - if not almost always -see in beginning database attempts.
I will not belabor the point any further. I do respect the effort it takes to attempt instruction of this stuff. But it has to be done very carefully or it causes as many problems as it solves...
January 18, 2011 at 10:55 am
kenambrose (1/18/2011)
<<To trash this article and say it doesn't provide good information is a fairly poor commentary. >>I respectfully disagree. To provide a teaching example for a database design that allows- ney almost guarantees - that if it was actually used it would result in:
1: numerous duplicate customer records
I disagree since Bob is directly working with his data, however, part two would be the introduction of unique indexing.
2: inccurate reports of data in receipts
Depends on what you consider inaccurate. A changed name after marriage, agreed this could be erronious. A changed company name but not a changed FedTaxID and you need to state any reprints as the current legal entity.
3: defines only surrogate keys which directly contributes to 1 and 2 above.
Betty the Secretary and Bob the Business Guy do not care about relational theory. They care about the work getting done. They are used to more manual processes and if they scrag the data, they're the only ones in there who need to fix it.
is simply not a good instructional example for beginners in my opinion. Especially since those two flaws are *exactly* the type of nonsense we often - if not almost always -see in beginning database attempts.
And there's a reason for that. DBs are an iterative learning process. How do you explain to someone who hasn't shot themselves in the foot to not ride in the pickup while resting the barrel there? Theory and practice, while related, are usually mentally disconnected as to importance.
They have to start somewhere. These are not database professionals, they are merely people trying to get a job done. They don't WANT to be database professionals, nor do they want to read 100 page dissertations on key methodology. They want to get their invoices out. If it's going to take them a week to learn how to do one aspect of it (keys)... they're going to print it from Excel.
I will not belabor the point any further. I do respect the effort it takes to attempt instruction of this stuff. But it has to be done very carefully or it causes as many problems as it solves...
... and to your final point. This keeps us employed. 🙂 You're thinking a multi-user environment. These are single user introductions to get a task done, which may eventually develop into the need for a professional review.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 16 through 30 (of 125 total)
You must be logged in to reply to this topic. Login to reply