Re: Practical - Design - With Dirty Hands

Fred. wrote:

> 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.

(And we're off! :-)

That is two incorrect and misleading statements right off the bat.

First of all the "data model" is pretty clearly the relational model. You are referring to the conceptual model or something else, but not the data model.

Secondly, if he is using the relational model then he does NOT want to model some area of the business, he wants to model the form of the assertions he needs to be able to make *about* the part of the business that is of interest.

> 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.

Wrong too, though your next paragraph does get nearer the truth. In general NO ONE understands a business. Each person involved will in general have a different point of view of everything. The picture of the business that eventually emerges is unlikely to resemble anything any one person said about it. However it will accommodate everything anyone wants to say about it.

This is important to point out to a newbie because they will otherwise be distressed that their model doesn't look "right".

> 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.

Please name these standards so that we can properly criticise them for introducing a means of making data corruption and logical modelling errors undetectable.

> Natural-seeming identifiers have a tendency to change with
> the business.

If the business changes the conceptual model has to change. Hiding the changes in the application code and coopering up the database design to get away with it is a professional misconduct and a sure source of peformance and logical errors. It is possibly also a violation of federal law in the US (e.g. Sarbanes-Oxley).

> 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.

No, it invalidates the conceptual model and requires it to be fixed properly.

> A mulit-field identifier is subject to the same risks as other "natural"
> identifiers but increased by the multiple fields.

Nonsense. You are sweeping the data quality issue under the carpet instead of addressing it. Natural identifiers (composite or otherwise) are superior precisely BECAUSE they can be known to be wrong. Synthetic "identifiers" conceal the error forever. You are adocating using precautionary anaesthetic instead of taking careful aim when using a hammer.

> So, multi-field
> realitonships should be reserved for emergencies when you must denormalize
> and, so, need to enforce consistency beween denormalied entities.

Utter, utter twaddle.

OP: use natural keys where possible, and if it's an SQL database make sure the foreign keys are declared ON UPDATE CASCADE. The guidelines for choosing keys are: uniqueness, stability, familiarity and simplicity. (And stability does not mean immutability.)

-- 
Roy