Re: Practical - Design - With Dirty Hands

On Wednesday, April 25, 2012 11:47:51 PM UTC-4, flebber wrote:
> Hi
>
> Just trying to find some good information on database design. I have
> got a decent grasp on table layout design and normalization. Well as
> much as a newbie can, my current project I am working on though I
> notice I need a better understanding of 'bringing it together' that is
> creating good multi field relationships and creating an effective
> model.
>
> Are there any good resources web/books/video which demonstrate in
> practical terms how to do this?
>
> Need to get my hands dirty to ensure my understanding and I would
> prefer some more complex examples that move beyond the simple
> customers/orders/inventory basic examples that are often prevalent and
> unrealistically simple. This is my first project for my college
> diploma and even at this semi basic level those types of examples
> aren't helpful.
>
> they say we learn best by copying and adapting off examples and others
> more experienced, but I can't find the resources that do this well.
>
> Sayth

You need to recognize that your data model is a model of some area of your business. That means that unless you are a one-person business that you must recobnize that others, not you, may be the experts on the business components: enttities, relationships, processes, and business rules that apply in this area.

Conversation with these experts, even in areas you think you understand well, are important because what appear to be solid may be transitory. For instance, the meaning of an attribution which you think you understand may vary with context. Looking at the design for another business probably won't help you nearly as much as listening to these people. You need to be tactful. You may be asking some very intimate questions from their point of view. So, you need to make it clear you are asking about their processes to facilitate them rather than to critique them.

Every database standard I have ever worked with has required a designed identity for a primary key and used this for the basis of implementing relationships. Natural-seeming identifiers have a tendency to change with the business. For instance, a business may suddenly have an overlapping old and a new series of invoice numbers, making the invoice number useless for a primary key in your database.

A mulit-field identifier is subject to the same risks as other "natural" identifiers but increased by the multiple fields. So, multi-field realitonships should be reserved for emergencies when you must denormalize and, so, need to enforce consistency beween denormalied entities.

Fred. Received on Thu Apr 26 2012 - 15:34:55 CEST