Re: Complex SQL

Hi,

my favourite opinion is: "It depends!"

The more experience I gain in the field of requierements analysis/db-design (only 5 years, not much compared to the gurus in the ng), the more I try to avoid to make decisions for myself about which business rules are valid and which not. If something is unclear, if there are questions and uncertainties, I have to ask! Surely often I got to take the customer by the hand and help him to get the business rules nailed down. Surely I have to think in advance to be able to incorporate new business rules without rebuilding the whole system. But if the rules are clear, everything what enforces them, what keeps the system running, what eases implementation, what makes the system running faster, is ok.

In the first order I normalize everything. That's perfectly ok and resonable to understand the data and the rules, which the contents of the db has to follow.

But sometimes de-normalization comes in handy. You have to do it consiously. You have to know what you do and why you do it. If there are reasons to do it, fine. Please see my suggestion not as "sloopy design" but as "food for thought". If it doesn't work because of further requierements, discard it. If it fits in well, why not use it? Sometimes breaking the rules makes life easier.

On Tue, 02 Apr 2002 15:39:03 GMT, "Ashish Mittal" <mittalashish_at_yahoo.com> wrote:

>Do not denormalize. Imagine doing a query which finds out the coach for all
>players in the system :)
>"Marc Blum" <marc_at_marcblum.de> wrote in message
>news:3ca85f6e.1755754_at_news.online.de...
>> On Mon, 1 Apr 2002 04:12:23 -0500, "Art Scott"
>> <as33289898002323232_at_hotmail.com> wrote:
>>
>> >Team[TeamID(pk), coach, rating]
>> >Player[PlayerID(pk), TeamID(fk)]
>> >
>> >Want to prevent data entry from having more than 12 players for each
>team.
>>
>> Denormalize!
>>
>> CREATE TABLE team
>> (
>> teamid number primary key,
>> coach varchar2(100) NOT NULL,
>> rating varchar2(10),
>> player_01 VARCHAR2(100),
>> player_02 VARCHAR2(100),
>> player_03 VARCHAR2(100),
>> player_04 VARCHAR2(100),
>> player_05 VARCHAR2(100),
>> player_06 VARCHAR2(100),
>> player_07 VARCHAR2(100),
>> player_08 VARCHAR2(100),
>> player_09 VARCHAR2(100),
>> player_10 VARCHAR2(100),
>> player_11 VARCHAR2(100),
>> player_12 VARCHAR2(100)
>> );
>>
>> Your business rule is:
>> A team consists of 0 to 12 player.
>> A team may not have more than 12 player.
>>
>> This structure enforces this business rule.
>>
>> What I don't know:
>> A player may only belong to one and only one team.
>> OR
>> A player can belong to none, one or many teams.
>> OR
>> (Further possibilities...)
>>
>> regards
>> Marc Blum
>> mailto:marc_at_marcblum.de
>> http://www.marcblum.de
>
>

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Tue Apr 02 2002 - 12:50:11 CST