An Introduction to Database Design

  • Lempster (2/14/2011)


    Brilliant and funny article Paul. I wonder if Daniel Vettori really does wear wasabi lip balm?! 😛

    He does, and he buys it from Bob :laugh:

  • sknox (1/20/2011)


    mike_walsh (1/18/2011)


    This is quite possibly one of the best technical articles for a wide audience that I have read in a long time, Paul. To anyone who is already well acquainted with database design, there won't be much new here...

    I respectfully disagree (not with the first sentence, with the second!) I think the breadth of the comments in this article show that one key thing that is "new" here to many DB professionals (myself included) is the end-user perspective. We spend so much time sitting in front of our screens modelling diagrams or tweaking scripts or reports that it's easy to forget how to help our users understand and get comfortable with tools that can help them a great deal. We get bogged down in query efficiency and degrees of normalization and can lose sight of the whole reason we're doing that.

    I read this article and the comments not to learn how to design a database, but to remember what drives people to databases and to get ideas on how to introduce the concepts without overloading people with technical jargon. This article does a great job of illustrating that.

    The same for me. It's a great article.

    A lot of theorists forget that while abstruse theory is fun it doesn't help those who don't want to learn a new science and new jargon to start talking about Boyce Codd Normal Form and Heath's Theorem and whether Ted Codd was a nutter when he dreamt up NULL, and all the rest of that stuff. If I have to get basic concepts about organising data across to someone I will try to do it Paul's way - I don't want to introduce the idea of time-dependent data until after I have sold the basic idea that you have keys and try to structure things so that the keys tell the whole story. The business guys know what the whole story is, probably better than I do, because business is their job, while IT is mine - if they need time-dependent data they'll tell me and we can talk about how to do it. This keeping the theory technical jargon out of initial education and only bringing it in once there's already enough understanding to make it meaningful is something I've learnt the hard way (more than once, as I tend to forget it) and I'm very happy to see another reminder of it.

    Tom

  • kenambrose (1/22/2011)


    duplicates never exist in the real world so preventing them is a basic step in recording facts about the world

    Last time I looked at physics four kinds of elementary bosons had been observed (and another two postulated, but not yet observed). Bosons can cerainly be duplicates (unlike Fermions) - and elementary bosons can of course occupy the same spaces as each other as well as having identical quantum states and being duplicates of each other. As neither the Higgs boson nor the graviton has yet been observed (and the latter probably never will be) I won't count those, and neither will I count composite bosons which are constructed from Fermions, but that still leaves the four already observed gauge bosons (photon, gluon, W, and Z) as real word things that can be (and frequently are) duplicated.

    So while relational theory doesn't like duplicates, don't make a fool of yourself by claiming that this is because it represents the real world, because the opposite is true: the absence of duplicates prevents it from modelling all aspects of the real world.

    Tom

  • In the presence of the element "Administratium", it's all for naught, anyway. 😉

    --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)
    Intro to Tally Tables and Functions

  • Jeff Moden (3/29/2011)


    In the presence of the element "Administratium", it's all for naught, anyway. 😉

    Is that anything like the Illuminati??

  • FargoUT (3/29/2011)


    Jeff Moden (3/29/2011)


    In the presence of the element "Administratium", it's all for naught, anyway. 😉

    Is that anything like the Illuminati??

    Heh... nah... at least the Illuminati get things done. 😀

    --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)
    Intro to Tally Tables and Functions

  • Very good article, thanks for this. I did Database design on my HND and hated it, no surprise that I failed it and had to retake it before I was awarded my HND. So I actually took to the time to learn it properly and passed the module. Six years later I become a SQL DBA, and start using normalization in the "real world" 😀

    All noobie DBAs should read this. Then read it again. And again. 😉

    fc

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
  • Well I thought it was excellent article

  • I think I'm in love with Alice.

  • That's a smashing article Paul - I've been designing databases for as long as I can remember and yet it remained an engaging and enlightening piece. Very well done.

  • Great article Paul 🙂

  • I have just read this article for the first time and found to be very informative. Have you followed it up with an article on T-SQL and, if so, is there a link?

  • I have just read this article for the first time and found to be very informative. Have you followed it up with an article on T-SQL and, if so, is there a link?

  • Excellent article for the db beginner, Paul. I'm a newbie to this forum but felt compelled to tell you how much I appreciate this article for it's elegant simplicity in explaining the merits of databases. I am excited to share it with co-workers who are just now learning about databases and coming over to the SQL side. Many thanks.

  • Very well written article - - clear and succinct. And the story approach makes it more engaging. 5 stars.

    For discussion (not a criticism):

    Regarding the bridge table - - because you have added quantity to the Receipts-Product table, I would think of this table as a fact table. If the purpose of the table was purely for resolving the many-to-many for queries for business information, and not to select quantity, then I would think of that as a bridge table.

    If you're going to put quantity in the table, you're more than likely also going to put date, and now the true transactional purpose of the table becomes obvious. And with any transactional table, your non-date keys will probably repeat - so now it is clearly NOT a bridge table.

    bjf

Viewing 15 posts - 91 through 105 (of 126 total)

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