January 18, 2011 at 11:12 am
part 2+ could discuss the ongoing success of Bob's business:
Returning customers update their address info, Alice is rightly concerned about the loss of information when Bob overwrites the old records with new data.
Alice's excellent reporting indicates an easy win by changing the price of a popular product. Bob helps himself to the price data and retroactively makes 3 years worth of accounts due for the difference. How does Alice fix it?
Bob hires an assistant salesperson who actively works on new orders while Bob is asking questions of the data. Concurrency issues caused by the "give me everything" approach and how to solve them.
How does Alice know when does Bob's business has outgrown SQLServer Express?
January 18, 2011 at 11:23 am
Mike Dougherty-384281 (1/18/2011)
part 2+ could discuss the ongoing success of Bob's business:Returning customers update their address info, Alice is rightly concerned about the loss of information when Bob overwrites the old records with new data.
Alice's excellent reporting indicates an easy win by changing the price of a popular product. Bob helps himself to the price data and retroactively makes 3 years worth of accounts due for the difference. How does Alice fix it?
Bob hires an assistant salesperson who actively works on new orders while Bob is asking questions of the data. Concurrency issues caused by the "give me everything" approach and how to solve them.
How does Alice know when does Bob's business has outgrown SQLServer Express?
I would certainly want to read that article.
(Paul, this is a small hint)
🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 18, 2011 at 11:28 am
Koen (da-zero) (1/18/2011)
Mike Dougherty-384281 (1/18/2011)
part 2+ could discuss the ongoing success of Bob's business:Returning customers update their address info, Alice is rightly concerned about the loss of information when Bob overwrites the old records with new data.
Alice's excellent reporting indicates an easy win by changing the price of a popular product. Bob helps himself to the price data and retroactively makes 3 years worth of accounts due for the difference. How does Alice fix it?
Bob hires an assistant salesperson who actively works on new orders while Bob is asking questions of the data. Concurrency issues caused by the "give me everything" approach and how to solve them.
How does Alice know when does Bob's business has outgrown SQLServer Express?
I would certainly want to read that article.
(Paul, this is a small hint)
🙂
I, too, would love to read it and I think it would be a great article for either Paul or Ken to write (or both! mwoohahaha 😀 )
__________________________________________________
Mike Walsh
SQL Server DBA
Blog - www.straightpathsql.com/blog |Twitter
January 18, 2011 at 1:24 pm
part 3 can be about Bob trying to migrate to Cassandra DB and it's 2 billion column/table limit
January 18, 2011 at 1:26 pm
It's a very well constructed piece but the approach to keys also bothers me. The article constantly refers to "the key" and dependencies on that one key. As a primer on database design this is potentially very misleading. The point of normalization is that it is equally concerned with ALL the candidate keys of a table and not just one key per table. So yet again a potential misconception about "primary" keys is apparently being supported (intentially or not).
This would be OK because apparently the intention of the example is to show tables with just ONE key each. However, there is such a weight of misinformation on this point that a better approach would be to try to redress the balance rather than give an example so similar to many others on the same topic.
January 18, 2011 at 1:27 pm
the database needs a few more tables
an inventory table to track inventory and will link back to the products table
an orders table
an inventory purchases table
January 18, 2011 at 1:52 pm
Superb introductory article. 'tis a keeper!
January 18, 2011 at 2:52 pm
Great article.
January 18, 2011 at 2:55 pm
I really enjoyed your article Paul. This is the way I prefer to learn, via a practical example. I think this article is exactly how to teach database design to people who are unfamiliar with databases. You don't want to look at this article from the point of view of an experienced database developer. Yes the design is not "right" yet, but the database will do what Bob needs it to do for now. Later, he can learn how to further improve his database.
This is approximately how I learnt to design databases and my knowledge has grown with each mentor I have had and with the challenges each new database has provided. Everyone makes mistakes with new experiences and then you can learn from them.
Cheers,
Nicole Bowman
Nothing is forever.
January 18, 2011 at 3:20 pm
Excellent Article Paul. I hope many people will find it useful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 18, 2011 at 3:25 pm
This is a decent, simple, and very early rough-out of a database design. I doubt the author intended this to be the final database design.
I particularly enjoyed the story as I have run across more than a few databases that were designed with help of a friend who had only brief university exposure to design. :crazy:
Bob will have to bypass Alice and bring in someone who can complete the design.
Nice first step explanation, though.
January 18, 2011 at 5:42 pm
Thanks everyone for the comments, both here and by email.
At the risk of stating the painfully obvious, of course the design has limitations, and yes in some important respects the paper receipts are superior.
The point is that learning good database design is a process (as Steve mentioned). The approach I chose was to introduce a couple of really important concepts per article. I was very conscious of the need to avoid overwhelming those for whom this is a completely new way of thinking.
On balance, I decided that the introduction was quite challenging enough without rushing into discussions of changes over time (e.g. prices), surrogate versus natural keys, multiple candidate keys...and so on and so on.
So, yes, as most of you seem to have guessed: Alice and Bob will return 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 18, 2011 at 6:50 pm
I took a "Fundamentals of Database Design" class last semester (for credit only; it was insulting since I am pretty well-versed in fundamental DB design already through self-training). And you put in one article what took my professor an entire semester to discuss. Such a total waste of time, that class was.
As for the argument going on about the examples portrayed in the story, I think this is a very sound and reasonable introduction to database design. The key word is "introduction". There will be much a DB designer will still need to tackle before implementing a proper system. As far as an introduction goes, this is one of the finest articles I've read. Concise, entertaining, applicable to real-life situations.
January 18, 2011 at 10:26 pm
This is a excellent article!!! Great work Paul....
January 19, 2011 at 7:17 am
Well done. I just sent your article to our new member. It should be a great reading for
1st day at new job.
So, yes, as most of you seem to have guessed: Alice and Bob will return 😉
Looking forward.
Viewing 15 posts - 31 through 45 (of 125 total)
You must be logged in to reply to this topic. Login to reply