Re: Normalising a two-to-one relationship

David Cressey wrote in message ...
>The two table solution you outline is more normalized than
> the one table solution you are contemplating.
>
>Having said that, the next question is, which design is
>better? It's possible that the one table solution will serve your needs
>better, regardless of normalization. Remember, normalization is
>a design principle, not an absolute golden rule.
>

Hi,

A couple of issues:
1) If you want to query all fixtures in which a team has participated (both home and away), you probably want to use the two-table version. 2) If you want to make sure that the user registered two and only two teams in each fixture, then the one-table version may be easier to program for (less complex constraints).
3) A variation of the above: If you want to register fixture-level information per team (e.g. each team's score) and want to make sure that either no scores or both scores ar registered, then the one-table version may be easier to program for.
4) If you use a CASE-like tool to generate your applications, the two-table version may give you difficulties in creating an intuitive UI for an application showing multiple fixtures. ("In the top half, you see the last five fixtures. In the bottom half, you see the two teams participating in the one fixture selected in the top half").

I always normalize first, and then consider denormalisation if the need arises. In general, it seems to me that query-oriented systems end up with a more normalized data model than data manipulation-oriented systems do.

--
Roy Brokvam