Re: database design question


"steven acer" <dudesterr@xxxxxxxxx> wrote in message
news:1184310851.449040.306880@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
hi ,

while designing a database with huge tables that are meant to hold
hundreds of thousdands of rows,

is it a good idea to flatten the tables a bit in order to accomodate
better for queries that require many joins ?

i know that this contradicts with normal forms and puts database
integrity on stake but if it is implemented what

could be the possible benefits and down sides ?

thanks


Hundred of thousands of rows is not sufficiently large, in and of itself, to
motivate departure from normalisation for performance reasons. How
performance gets affected depends of which DBMS and which version you are
using, what your hardware platform is, and a host of other variables.

I have often departed from normalisation, and designed databases with data
joined together in ways that contradict one of the normal forms. This was
almost always in the context of a reporting database, or a database to
support OLAP style activities. The price you pay for making this choice is
that the processes that keep the data up to date are harder to write and run
slower.

My one strong recommendation is that, is you do go in such a direction, that
you follow some other design discipline, one that will help you make
considered judgements about how the denormalized data should be composed.
Avoid just joining data because "it seemed like a good idea at the time."

The design discipline I have followed for reporting and OLAP databases is
star schema. You can learn about it under "data mart" or "data
warehousing". Don't be intimidated by the term "data warehousing". Star
schema is a design discipline that can be scaled down to the size problem
you are working on. It's not hard to learn, but it's hard to accept if
you've already learned how to design normalized data.

I can't say that you really need to denormalize. But if you do, I heartily
recommend star schema design.


.

Relevant Pages

  • Re: Combo Box AfterUpdate Help
    ... its a shame that Morrison now finds it so ... >> "I have two tables in an ordering system database. ... >> in compliance with the rules of normalisation. ... >> possibility ) for being hesitant about your design. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Query to search multiple identical tables
    ... separate from the newsgroups and the advices given in these newsgroups are ... whether you referred to making your database, with you as the developer, as ... correct and then to design easy and intuitive GUI for your users. ... know how to apply the Database Normalisation technique). ...
    (microsoft.public.access.queries)
  • C# programmer looking for a job
    ... Software Development including Desktop, Client/Server and Database ... Practical skills in object oriented design and design patterns ... XML, Oracle, CVS, VSS, Delphi, bug tracking. ... Developed in Delphi5; ...
    (misc.immigration.usa)
  • Re: O/R Mapper
    ... | - create E/R model from niam model ... classes that contain, not only data, but also functionality as OO design is ... a database where they do not exist in the object model is corrupting the ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Date range on reports
    ... > box to your report with a control source like: ... >> In the Database window (Database window: The window that appears when you ... >> In the New Form dialog box, click Design View, and click OK. ... >> Begin by clicking Macro Names to display the Macro Name column. ...
    (microsoft.public.access.reports)